CODEWITHSUNDEEP
sqloracle-databasesum
Badmiral
Badmiral

Reputation: 1589

running sum on group by

I have this query

SELECT  NAME, OTHER_NAME, COUNT(NAME)
FROM ETHNICITY
GROUP BY NAME,OTHER_NAME

and I would like to add a running sum on other_name or name in that column.

For instance, if there is 3x african american and 2x name= "other" and other_name = "jewish" I want to give it 3 and 2 as the counts and sum them up as it traverses

Any ideas how I can augment this to add that? Thanks.

Upvotes: 0

Views: 703

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I prefer to do this using a subquery:

select t.name, t.other_name, t.cnt,
       sum(cnt) over (order by name) as cumecnt
from (SELECT  NAME, OTHER_NAME, COUNT(NAME) as cnt
      FROM ETHNICITY
      GROUP BY NAME,OTHER_NAME
     ) t

This assumes that you want a cumulative sum of count in the order of name.

The order by in the analytic functions do cumulative sums. This is standard syntax, and also supported by Postgres and SQL Server 2012.

The following might also work

select name, other_name, count(name) as cnt,
       sum(count(name)) over (order by name)
from ethnicity
group by name, other_name

I find this harder to read (the sum(count()) is a bit jarring) and perhaps more prone to error. I haven't tried this syntax on Oracle; it does work in SQL Server 2012.

Upvotes: 2

xQbert
xQbert

Reputation: 35343

Look at Grouping sets, lets you aggregate totals.

Not sure this is what you're after though...

SELECT  NAME, OTHER_NAME, COUNT(NAME)
FROM ETHNICITY
GROUP BY GROUPING SETS ((NAME,OTHER_NAME), (Name), ())

Sorry ID10T error... the grouping sets didn't require a 2nd aggregate, the count will do it on it's own:

So this data:

Name   Other_Name
A       B
A       C
A       D
B       E
B       F
B       G
C       H
C       I
C       J

Results in 

Name   Other_Name   CNT(Name)
A      B           1
A      C           1
A      D           1
A                  3
B      E           1
B      F           1
B      G           1
B                  3
C      H           1
C      I           1
C      J           1
C                  3
                   9

Upvotes: 1

Andomar
Andomar

Reputation: 238296

In Oracle, a running sum is easily done with the sum() ... over() window function:

select  name
,       other_name
,       name_count
,       sum(name_count) over(
            order by name, other_name) as running
from    (
        select  name
        ,       other_name
        ,       count(name) as name_count
        from    ethnicity
        group by 
                name
        ,       other_name
        order by
                name
        ,       other_name
        ) subqueryalias

Example at SQL Fiddle

Upvotes: 2

Related Questions