Общая сводка с несколькими GROUP BY

Хорошо, я наконец придумал два подхода, которые гибки и не заставляют меня чувствовать себя ужасным программистом.

Первое решение включает GROUPING SETS.
По сути, я пытаюсь сгруппировать выражение на двух разных уровнях: один на общем уровне и один на уровне (country, province).

Если бы я разделил запрос на две части и использовал UNION ALL, в одной половине было бы GROUP BY country, province, а в другой не было бы предложения группировки. Негруппированный раздел также может быть представлен как GROUP BY (), если вам так хочется. Это пригодится через мгновение.

Это дает нам что-то вроде:

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 ();

Запрос работает, но плохо масштабируется. Чем больше вычислений вам нужно сделать, тем больше времени вы тратите на повторение.

Используя GROUPING SETS, я могу указать, что я хочу, чтобы данные были сгруппированы двумя разными способами:

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

Теперь мы получаем где-то! Но как насчет нашей строки результатов? Как мы можем обнаружить его и обозначить соответствующим образом? Вот где на помощь приходит функция GROUPING. возвращает 1, если столбец имеет значение NULL из-за оператора GROUP BY.

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), () );

Если нам не нравится подход GROUPING SETS, мы все равно можем использовать традиционный ROLLUP, но с небольшим изменением.

Вместо того, чтобы передавать каждый столбец в ROLLUP по отдельности, мы передаем набор столбцов как набор, заключая их в круглые скобки. Благодаря этому набор столбцов рассматривается как одна группа, а не как несколько групп. Следующий запрос даст вам те же результаты, что и предыдущий:

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

Не стесняйтесь попробовать оба подхода!
http://sqlfiddle.com/#!4/12ad9/102

В Oracle вы можете сделать это с помощью предложения having:

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;

Здесь скрипт SQL.

Первое, что приходит на ум, это отфильтровать промежуточные итоги после применения rollup:

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

Вы можете сделать то же самое немного более компактно, используя GROUPING_ID в предложении HAVING:

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

И, как указал @Anssssss, вы также можете использовать критерии из предложения WHERE в первом ответе в предложении HAVING:

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

Вы можете использовать Union:

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

Я придумал sql, используя Union, чтобы добавить Total в конец ваших результатов. Вы можете увидеть запрос здесь

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;