GROUP BY GROUPING SETS

Question

I am writing Oracle SQL query; where i need to compute subtotals along with my normal query results. For this i decided to use GROUP BY Extension (GROUPING SETS)

Due to privacy issues i am not sharing actual query. Instead i am writing below an imaginary query which best describe my subtotal requirement

SELECT  
    region,
    country,
    province,
    city,
    SUM(agg_col1) agg_col1,
    SUM(agg_col2) agg_col2
FROM tbl_name
GROUP BY GROUPING SETS(region)

Now i know above query will give an error because not all non-aggregate columns are part of GROUP BY expression; but this is the actual point where i got stuck and i am clue less how to achieve desired results. My only requirement is to get region wise subtotals and grand toal along with query results.

I will also appreciate if some detailed reading material or video is recommended for mastering this.

EDIT Suppose below is some sample data I want the sum of regions e.g consider below data Region Country Province City Agg 1 Agg 2

r1  c1  p1  ct1 1   1
r1  c1  p1  ct2 1   1
r1  c1  p2  ct1 1   1
r1  c1  p2  ct2 1   1
r1  c2  p1  ct1 1   1
r1  c2  p1  ct2 1   1
r1  c2  p2  ct1 1   1
r1  c2  p2  ct2 1   1
r2  c1  p1  ct1 1   1
r2  c1  p1  ct2 1   1
r2  c1  p2  ct1 1   1
r2  c1  p2  ct2 1   1
r2  c2  p1  ct1 1   1
r2  c2  p1  ct2 1   1
r2  c2  p2  ct1 1   1
r2  c2  p2  ct2 1   1
Along with above 16 rows i need following 3 rows
r1              8   8
r2              8   8
                16  16

Can you add some sample table data, and the expected result? — jarlh, Feb 11 '16 at 11:09
I'm not sure I under stood you.. you want the sum of what? of each region? region,country? or what — sagi, Feb 11 '16 at 11:09

score 1 · Answer 1 · answered Feb 11 '16 at 11:21

Try this query:

SELECT  
    region,
    max(country),
    max(province),
    max(city),
    SUM(agg_col1) agg_col1,
    SUM(agg_col2) agg_col2
FROM tbl_name
GROUP BY  ROLLUP (region)

The max basically is to avoid the error that you have to put those columns in the group by, it will pick the max value. You can change it to min or if you want a specific value tell me and I'll adjust it..

Group by rollup will basically give you total row.

You explanation wasn't very good so tell me if thats what you meant.

Thorsten Kettner · Answer 2 · 2016-02-11T12:39:16.750

Im must admit that I have no experience with GROUPING SETS, so I don't know if this would offer a good solution ort not.

Usually you'd build totals with ROLLUP instead. Then you can apply a HAVING clause on GROUPING(x) to exclude subtotals from the results:

SELECT  
  region,
  country,
  province,
  city,
  SUM(agg_col1) agg_col1,
  SUM(agg_col2) agg_col2
FROM tbl_name
GROUP BY ROLLUP(region, country, province, city)
HAVING GROUPING(country) = GROUPING(provice) AND GROUPING(country) = GROUPING(city)
ORDER BY region, country, province, city;

If you want the totals at the end of your query, then change your ORDER BY clause to:

ORDER BY GROUPING(country), region, country, province, city;

BTW: In the HAVING clause we want to make sure the three values all hold the same value (all 0 or all 1). There is no function like ALL_EQUAL(a,b,c) or the like, so I used two comparisions combined with AND. However, if you find it more readable, you can change the HAVING clause to:

HAVING GROUPING(country) = ALL( GROUPING(provice) , GROUPING(city) )

GROUP BY GROUPING SETS

2 Answers2