getting a sum of a "Grouped by" query

Toad for Oracle
1

is it possible to sum the totals of a “grouped by” query?

My query syntax is below:

select b.region, count (b.parentcode) from pers a, parentunit b where a.parentcode=b.parentcode and a.empstatus = ‘ACT’ group by b.region;

REGION COUNT(B.PARENTCODE)

SOUTHERN REGION 3778

NORTHERN REGION 2883

HEADQUARTERS 804

3 rows selected.

2

I don’t know if there is a way to get a total as a separate row at the bottom, but you can use analytic functions to give you a running total as you go down the list…

Here are some good examples of analytic functions

http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23sql-1906475.html

If you can’t make that work in conjunction with COUNT/GROUP BY, you could always move your query up into a sub query.

I haven’t tested this, but I think it will work with your data to give a running total.

with data as
(select b.region, count (b.parentcode) parentcode_count
from pers a, parentunit b
where a.parentcode=b.parentcode
and a.empstatus = ‘ACT’
group by b.region)
select region, parentcode_count,
SUM (parentcode_count) OVER (ORDER BY region) running_total
from data
order by region;

3

Hey Joe,

Don’t know what the overall goal is, but if you want to get the full sum on a per-row basis, try this using the sum analytic:

select b.region, count (b.parentcode) knt, sum(count (b.parentcode)) over () sum_of_count

from pers a, parentunit b

where a.parentcode=b.parentcode and a.empstatus = ‘ACT’

group by b.region;

– you could then wrap this query to get percentages and other useful algorithmic info

Alternately, if you’re doing a quick sql*plus report (old school, not TOAD!) you can try:

break on report

compute sum of knt on report

select b.region, count (b.parentcode) knt

from pers a, parentunit b

where a.parentcode=b.parentcode and a.empstatus = ‘ACT’

group by b.region;

Good Luck!

4

You can use the CUBE function to get the total. I have included an example below…

David

SELECT TYPE_CD, SUM(CNT) CNT
FROM (SELECT 'Balls' TYPE_CD, 1 CNT FROM DUAL
UNION ALL
SELECT 'Jacks' TYPE_CD, 4 CNT FROM DUAL)
GROUP BY CUBE(TYPE_CD)
ORDER BY 1

5

The “group by cube” option seems to be pretty slick and efficient!