help with using SUM in Select statement

[dharkangel]

Hello,

I have to select multiple fields to get the sum and although the following example works great for selecting one field, I need to be able to select multiples. I tried the code below and I get an syntax error.

SELECT (SELECT Sum(ProgramManagementWeeklyClosedByCommit AS PMCBC, ProgramManagementWeeklyTotalClosed AS PMTotal)) FROM QPTClosedByCommitByComponent Where ID<=A.ID AND ID>0 AND ID <6), A.ID
FROM QPTClosedByCommitByComponent AS a
WHERE ID>0 And ID<6
ORDER BY ID;

this doesn't work either:

SELECT (SELECT Sum(ProgramManagementWeeklyClosedByCommit, ProgramManagementWeeklyTotalClosed)) FROM QPTClosedByCommitByComponent Where ID<=A.ID AND ID>0 AND ID <6) AS PMMgtCBC, PMTotal, A.ID
FROM QPTClosedByCommitByComponent AS a
WHERE ID>0 And ID<6
ORDER BY ID;

thank you,

sal

 

[Golom]

Try

SELECT Sum(B.ProgramManagementWeeklyClosedByCommit) AS PMCBC, 
       Sum(B.ProgramManagementWeeklyTotalClosed) AS PMTotal, 
       A.ID

FROM QPTClosedByCommitByComponent AS B INNER JOIN QPTClosedByCommitByComponent As B
     ON B.ID<=A.ID  

WHERE B.ID BETWEEN 0 AND 6

GROUP BY A.ID

ORDER BY A.ID;

 

[PHV]

And what about something like this ?
SELECT Sum(B.ProgramManagementWeeklyClosedByCommit) AS PMMgtCBC, Sum(B.ProgramManagementWeeklyTotalClosed) AS PMTotal, A.ID
FROM QPTClosedByCommitByComponent AS A INNER JOIN QPTClosedByCommitByComponent AS B ON A.ID>=B.ID A
WHERE A.ID>0 AND A.ID<6 AND B.ID>0 AND B.ID<6
GROUP BY A.ID
ORDER BY A.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dharkangel]

thank you this seems to be working great.