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
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