Sub-totals with group by

[AIXFinder]

I have this query and the output:

select
ptloc, RMOutcomeAct, RMOutcomePot,
count (RMOutcomeAct) as ActCount,
count (RMOutcomePot) as PotCount,
sum(RxDoseAct) as ActSUM,
sum(RxDosePot) as PotSUM
from Report r, RMInvest m, PharmInvest p, AdminInvest a
where r.ReportNo = m.RMReportNo and m.RMReportNo = p.RxReportNo
and p.RxReportNo = a.ReportNo and convert (datetime, NurDateofOcc) between '01/01/2006' and '12/31/2006'
group by ptloc, RMOutcomeAct, RMOutcomePot
order by ptloc, RMOutcomeAct

but, I want the sub-total of sum(RxDoseAct) = ActSUM and sum(RxDosePot) = PotSUM per "Location", which is ptloc.

i.e.

The output gives:

ptloc .... ActSum ... PotSUM ...
2N 11 7
2N 17 19
2N 9 12
2S 5 3
2S 6 4

bla.....

What I want is:

ptloc .... ActSum ... PotSUM ...
2N 11 7
2N 17 19
2N 9 12
Sub-total for 2N 37 38
2S 5 3
2S 6 4
Sub-total for 2S 11 7
bla...


I want all of the columns from the existing query, and, on the top of those, I want the two sub-totals per ptloc like above.

thx much

 

[r937]

add the words WITH ROLLUP at the end of the GROUP BY clause

r937.com | rudy.ca