Overall summary with multiple GROUP BY

Question

Lets say I have a table called census with the following information:

COUNTRY     PROVINCE    CITY        POPULATION
==============================================
USA         California  Sacramento  1234
USA         California  SanFran     4321
USA         Texas       Houston     1111
USA         Texas       Dallas      2222
Canada      Ontario     Ottawa      3333
Canada      Manitoba    Winnipeg    4444

I'm building a report at the country/province level, which gives me the following:

SELECT country, province, SUM(population)
FROM census
GROUP BY country, province;

COUNTRY     PROVINCE    SUM(POPULATION)
=======================================
USA         California  5555
USA         Texas       3333
Canada      Ontario     3333
Canada      Manitoba    4444

I'm looking to have an "overall summary" row included on the report, so that the final result looks like:

COUNTRY     PROVINCE    SUM(POPULATION)
=======================================
USA         California   5555
USA         Texas        3333
Canada      Ontario      3333
Canada      Manitoba     4444
TOTAL                   16665

I'm acquainted with ROLLUPs, but I can't seem to find a combination that gets me what I'm looking for. Using GROUP BY ROLLUP(country, province) includes the total value I want, but it also includes a large number of extra values which I don't care about. This is also true with GROUP BY ROLLUP(country), province

How can I go about making the "total" record?
I'm currently calculating it with a UNION ALL and repeating 90% of the first query with a different GROUP BY, but because the first query is non-trivial, the result is slow and ugly code.

Here's a SQL Fiddle for those who want to play with this: http://sqlfiddle.com/#!4/12ad9/5

possible duplicate of [Add a summary row with totals](http://stackoverflow.com/questions/17934318/add-a-summary-row-with-totals) — Bulat, Sep 09 '14 at 21:50
@Bulat - That's for a single column in a `GROUP BY` and uses SQL Server specific syntax. — Mr. Llama, Sep 09 '14 at 22:18
Well, you can find the same answer there and give it a vote. If only the number of columns and their names were important... And still it is a +1 for the well presented question. — Bulat, Sep 10 '14 at 08:19

score 13 · Answer 1 · answered Sep 09 '14 at 21:33

13

This is exactly what GROUPING SETS expressions was designed to do:

SELECT country, province, SUM(population)
FROM census
GROUP BY GROUPING SETS
   ( (country, province),        -- first group by country and province
     ()                          -- then by (nothing), i.e. a total grouping
   );

See the SQL-Fiddle

answered Sep 09 '14 at 21:33

ypercubeᵀᴹ

113,259
19
174
235

Cool! Now how do I detect the summary row with the `GROUPING` function? I can't seem to pass it anything without it throwing an error. I also found out that `ROLLUP( (country, province) )` works too but you beat me to the answer. :D – Mr. Llama Sep 09 '14 at 22:17
Disregard my previous comment. `GROUPING` returns 1 when the column is null because of a ROLLUP, so `GROUPING(country)` and `GROUPING(province)` both work. – Mr. Llama Sep 09 '14 at 22:21
By the way (regarding your deleted answer), `GROUP BY 1` means something else usually. `GROUP BY ()` is actually valid SQL for an overall group by, equivalent to not having a group by clause.. – ypercubeᵀᴹ Sep 09 '14 at 22:40
1

@Mr.Llama: Please consider posting your `ROLLUP( (country, province) )` solution as an answer. It makes perfect sense in your scenario and I personally find it clearer than this answer, however much I may like upvoting ypercube's posts. – Andriy M Sep 10 '14 at 09:51
@Mr.Llama, AndriyM is right, the `GROUP BY ROLLUP( (country, province) )` should be an answer - and certainly not in the question. Please add it in your answer (and undelete.) – ypercubeᵀᴹ Sep 10 '14 at 15:17
1

@ypercube - Undeleted my answer and added an explanation for both the `GROUP BY` and `ROLLUP` approaches. – Mr. Llama Sep 10 '14 at 15:29
Hello sir, is it possible to get summary w.r.t each group i.e group summary? – Sagar Chaudhary Sep 28 '18 at 15:27
1

@SagarChaudhary yes, just add another grouping set. E.g.: `GROUP BY GROUPING SETS ( (country, province), (country), () );` to add summaries per country as well. – ypercubeᵀᴹ Sep 28 '18 at 15:32

Mr. Llama · Accepted Answer · 2014-09-10T15:28:03.813

Ok, I finally came up two approaches that are flexible and don't make me feel like a terrible programmer.

The first solution involves GROUPING SETS.
What I'm essentially trying to do is group the expression at two different levels: one at the overall level, and one at the (country, province) level.

If I were to split the query into two parts and use a UNION ALL, one half would have a GROUP BY country, province and the other would lack a grouping clause. The un-grouped section can also be represented as GROUP BY () if we feel like it. This will come in handy in a moment.

That gives us something like:

SELECT country, province, SUM(population)
FROM census
GROUP BY country, province
UNION ALL
SELECT NULL AS country, NULL AS province, SUM(population)
FROM census
GROUP BY ();

The query works, but it doesn't scale well. The more calculations you need to make, the more time you spend repeating yourself.

By using a GROUPING SETS, I can specify that I want the data grouped in two different ways:

SELECT country, province, SUM(population)
FROM census
GROUP BY GROUPING SETS( (country, province), () );

Now we're getting somewhere! But what about our result row? How can we detect it and label it accordingly? That's where the GROUPING function comes in. It returns a 1 if the column is NULL because of a GROUP BY statement.

SELECT
    CASE
        WHEN GROUPING(country) = 1 THEN 'TOTAL'
        ELSE country
    END AS country,
    province,
    SUM(population),
    GROUPING(country) AS grouping_flg
FROM census
GROUP BY GROUPING SETS ( (country, province), () );

If we don't like the GROUPING SETS approach, we can still use a traditional ROLLUP but with a minor change.

Instead of passing each column to the ROLLUP individually, we pass the collection of columns as a set by encasing them in parentheses. This makes it so the set of columns is treated as a single group instead of multiple groups. The following query will give you the same results as the previous:

SELECT
    CASE
        WHEN GROUPING(country) = 1 THEN 'TOTAL'
        ELSE country
    END AS country,
    province,
    SUM(population),
    GROUPING(country) AS grouping_flg
FROM census
GROUP BY ROLLUP( (country, province) );

Feel free to try both approaches for yourself!
http://sqlfiddle.com/#!4/12ad9/102

score 3 · Answer 3 · answered Sep 09 '14 at 21:28

In Oracle you can do this with a having clause:

SELECT coalesce(c.country, 'Total') as province, c.country, SUM(c.population)
FROM census c
GROUP BY ROLLUP(c.country, c.province)
HAVING c.province is not null or
       c.province is null and c.country is null;

Here is the SQL Fiddle.

Allan · Answer 4 · 2014-09-09T21:09:01.997

The first thing that comes to mind is filter out the sub-totals after the rollup is applied:

SELECT *
FROM   (SELECT   country, province, SUM (population)
        FROM     census
        GROUP BY ROLLUP (country, province))
WHERE  province IS NOT NULL OR country IS NULL;

You can accomplish the same thing a little more compactly by using GROUPING_ID in the HAVING clause:

SELECT   country,
         province,
         SUM (population)
FROM     census
GROUP BY ROLLUP (country, province)
HAVING   GROUPING_ID (country, province) <> 1

And, as @Anssssss pointed out, you can also use the criteria from the WHERE clause in the first answer in a HAVING clause:

SELECT   country, province, SUM (population)
FROM     census
GROUP BY ROLLUP (country, province)
HAVING   province IS NOT NULL OR country IS NULL

score -1 · Answer 5 · answered Sep 09 '14 at 20:58

-1

You could do use a Union:

SELECT country, province, SUM(population)
FROM census
GROUP BY country, province
UNION
SELECT
   'Total', '', SUM(population)
FROM census

answered Sep 09 '14 at 20:58

DLeh

23,806
16
84
128

2

The original poster said they were already doing a UNION and it was inefficient and "ugly". The SQL fiddle showed this in detail. They wanted to avoid doing a union. – Anssssss Sep 09 '14 at 21:15

score -1 · Answer 6 · answered Sep 09 '14 at 21:18

Ive come up with a sql using Union to add Total to the end of your results. You can see the query here

SELECT country, province, SUM(population) as population, 0 as OrderBy
FROM census
GROUP BY country, province
UNION
SELECT country, province, population, 1 as OrderBy FROM (
  SELECT 'Total' as country, '' as province, SUM(population) as population
  FROM census
)
ORDER BY OrderBy;

Overall summary with multiple GROUP BY

6 Answers6