little more help with UNION and SUM statement

[dharkangel]

hi again,

The query below is working great, I now need to do a union query on it. I am trying to create a query that does a running count but that resets at a specific interval. This is the reason you see the UNION being done at specific number intervals. Below is the query that is working:

SELECT Sum(B.ProgramManagementWeeklyClosedByCommit) AS PMMgtCBC, Sum(B.ProgramManagementWeeklyTotalClosed) AS PMMgmtTotal, A.ID
FROM QPTClosedByCommitByComponent AS A INNER JOIN QPTClosedByCommitByComponent AS B ON A.ID>=B.ID
WHERE A.ID>0 AND A.ID<6 AND B.ID>0 AND B.ID<6
GROUP BY A.ID
ORDER BY A.ID;

This is how I need it to work (of course this does not work, it's just an example)

SELECT Sum(B.ProgramManagementWeeklyClosedByCommit) AS PMMgtCBC, Sum(B.ProgramManagementWeeklyTotalClosed) AS PMMgmtTotal, A.ID
FROM QPTClosedByCommitByComponent AS A INNER JOIN QPTClosedByCommitByComponent AS B ON A.ID>=B.ID
WHERE A.ID>0 AND A.ID<6 AND B.ID>0 AND B.ID<6
UNION
SELECT Sum(B.ProgramManagementWeeklyClosedByCommit) AS PMMgtCBC, Sum(B.ProgramManagementWeeklyTotalClosed) AS PMMgmtTotal, A.ID
FROM QPTClosedByCommitByComponent AS A INNER JOIN QPTClosedByCommitByComponent AS B ON A.ID>=B.ID
WHERE A.ID>5 AND A.ID<10 AND B.ID>5 AND B.ID<10
GROUP BY A.ID
ORDER BY A.ID;

Thank you again,

 

[SkipVought]

Hi,

2 group bys...

SELECT 
  Sum(B.ProgramManagementWeeklyClosedByCommit) AS PMMgtCBC
, Sum(B.ProgramManagementWeeklyTotalClosed)    AS PMMgmtTotal
, A.ID

FROM       QPTClosedByCommitByComponent AS A 
INNER JOIN QPTClosedByCommitByComponent AS B 
   ON A.ID>=B.ID

WHERE 
      A.ID Between 1 and 5 
  AND B.ID Betweeb 1 and 5 

GROUP BY A.ID

UNION

SELECT
  Sum(B.ProgramManagementWeeklyClosedByCommit) AS PMMgtCBC
, Sum(B.ProgramManagementWeeklyTotalClosed)    AS PMMgmtTotal
, A.ID

FROM       QPTClosedByCommitByComponent AS A 
INNER JOIN QPTClosedByCommitByComponent AS B 
   ON A.ID>=B.ID

WHERE 
      A.ID Between 6 and 9 
  AND B.ID Betweeb 6 and 9

GROUP BY A.ID

ORDER BY A.ID;

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[dharkangel]

thanks, this seems to have worked.