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

Answer 1

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

Answer 2

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