Overall summary with multiple GROUP BY

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

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

See the SQL-Fiddle

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 censusGROUP BY country, provinceUNION ALLSELECT NULL AS country, NULL AS province, SUM(population)FROM censusGROUP 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 censusGROUP 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_flgFROM censusGROUP 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_flgFROM censusGROUP BY ROLLUP( (country, province) );

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

In Oracle you can do this with a having clause:

SELECT coalesce(c.country, 'Total') as province, c.country, SUM(c.population)FROM census cGROUP 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.