J'essaie d'get le nombre total de minutes mais cela ne fonctionne pas correctement avec mes jointures à gauche. Y a-t-il un moyen d'append une colonne qui a le total DISTINCT de minutes pour une MachineDescription donnée?
Voici le sqlfiddle: http://sqlfiddle.com/#!2/7fd99/1
SELECT Work.WorkID , Work.Description , Machine.MachineDescription , Name.NAME , Work2.RegMin , Work.MINUTES FROM Work Work JOIN Machine Machine ON Machine.MachineID = Work.MachineID LEFT JOIN Work2 Work2 ON Work2.WorkID = Work.WorkID LEFT JOIN Name Name ON Name.NameID = Work2.NameID
J'aimerais avoir une colonne avec la sum de minutes DISTINCT. Donc le total des minutes serait '30' et non '40' pour WorkID 111.
Ma sortie ressemble à ceci:
WorkID | Description | MachineDescription | Name | RegMin | Minutes ------------------------------------------------------------------- 111 Replace Belt Splitter Joe 10 10 111 Replace Belt Splitter Bob 20 10 112 Door Broke Splitter Joe 10 20
Je veux une colonne avec le total des minutes par DISTINCT WorkID comme ça …
WorkID | Description | MachineDescription | Name | RegMin | Minutes | Total Minutes ----------------------------------------------------------------------------------- 111 Replace Belt Splitter Joe 10 10 30 111 Replace Belt Splitter Bob 20 10 30 112 Door Broke Splitter Joe 10 20 30
Est-ce possible?
Utilisez la Correlated Sub-query
pour get le résultat. Essaye ça.
SELECT Work.WorkID, Work.Description, Machine.MachineDescription, NAME.NAME, Work2.RegMin, Work.MINUTES, (SELECT Sum(DISTINCT MINUTES) FROM Work w WHERE w.MachineID = Machine.MachineID) Total_minutes FROM Work Work JOIN Machine Machine ON Machine.MachineID = Work.MachineID LEFT JOIN Work2 Work2 ON Work2.WorkID = Work.WorkID LEFT JOIN NAME NAME ON NAME.NameID = Work2.NameID
Sortie:
WorkID Description MachineDescription NAME RegMin MINUTES Total_minutes ------ ----------- ------------------ ---- ------ ------- ------------- 111 Sink Broken SPLITTER Joe 10 10 30 111 Sink Broken SPLITTER Bob 20 10 30 112 Door Broken SPLITTER Joe 10 20 30
SELECT Work.WorkID , Work.Description , Machine.MachineDescription , Name.NAME , Work2.RegMin , Work.MINUTES , (SELECT (SUM(work2.RegMin) ) FROM work2 left join work on work2.workid = work.workid) - work.minutes AS TotalMinutes FROM Work Work JOIN Machine Machine ON Machine.MachineID = Work.MachineID LEFT JOIN Work2 Work2 ON Work2.WorkID = Work.WorkID LEFT JOIN Name Name ON Name.NameID = Work2.NameID GROUP BY work.workID , Work.Description , Machine.MachineDescription , Name.NAME , Work2.RegMin , Work.MINUTES
Résultats
WORKID DESCRIPTION MACHINEDESCRIPTION NAME REGMIN MINUTES TOTALMINUTES 111 Sink Broken SPLITTER Bob 20 10 30 111 Sink Broken SPLITTER Joe 10 10 30 112 Door Broken SPLITTER Joe 10 20 20
Ajoutez ceci à votre sélection:
(SELECT SUM(minutes) FROM (SELECT DISTINCT workid, minutes FROM WORK) W ) AS Total_Minutes