sql - learn - sequel database

Resumen general con múltiples GRUPO POR

Question

Digamos que tengo una tabla llamada census con la siguiente información:

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

Estoy elaborando un informe a nivel de país / provincia, que me da lo siguiente:

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

Estoy buscando tener una fila de "resumen general" incluida en el informe, para que el resultado final se vea como:

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

ROLLUP los ROLLUP , pero parece que no puedo encontrar una combinación que me ROLLUP lo que estoy buscando. El uso de GROUP BY ROLLUP(country, province) incluye el valor total que quiero, pero también incluye una gran cantidad de valores adicionales que no me importan. Esto también es cierto con GROUP BY ROLLUP(country), province

¿Cómo puedo hacer para hacer el registro "total"?
Actualmente lo estoy calculando con UNION ALL y repito el 90% de la primera consulta con un GROUP BY diferente, pero como la primera consulta no es trivial, el resultado es un código lento y feo.

Aquí hay un Fiddle de SQL para aquellos que quieren jugar con esto: http://sqlfiddle.com/#!4/12ad9/5

Answer 1

En Oracle puedes hacer esto con una cláusula have:

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 está el violín de SQL.

Answer 2

Esto es exactamente para lo que se diseñaron las expresiones GROUPING SETS :

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

Ver el SQL-Fiddle

Answer 3

He creado un sql usando Union para agregar Total al final de sus resultados. Puedes ver la consulta aquí

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;

Answer 4

Lo primero que viene a la mente es filtrar los subtotales después de aplicar el rollup :

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

Puede lograr lo mismo de forma más compacta utilizando GROUPING_ID en la cláusula HAVING :

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

Y, como señaló @Anssssss, también puede utilizar los criterios de la cláusula WHERE en la primera respuesta en una cláusula HAVING :

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

Answer 5

Ok, finalmente encontré dos enfoques que son flexibles y no me hacen sentir como un programador terrible.

La primera solución consiste en GROUPING SETS .
Lo que esencialmente estoy tratando de hacer es agrupar la expresión en dos niveles diferentes: uno a nivel general y otro a nivel (country, province) .

Si tuviera que dividir la consulta en dos partes y utilizar un UNION ALL , una mitad tendría un GROUP BY country, province y el otro carecería de una cláusula de agrupación. La sección no agrupada también se puede representar como GROUP BY () si lo deseamos. Esto será útil en un momento.

Eso nos da algo como:

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

La consulta funciona, pero no se escala bien. Cuantos más cálculos necesite realizar, más tiempo pasará repitiéndose.

Al usar GROUPING SETS , puedo especificar que quiero que los datos se agrupen de dos maneras diferentes:

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

¡Ahora estamos llegando a alguna parte! Pero ¿qué pasa con nuestra fila de resultados? ¿Cómo podemos detectarlo y etiquetarlo en consecuencia? Ahí es donde entra la función GROUPING . Devuelve un 1 si la columna es NULA debido a una instrucción 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), () );

Si no nos gusta el enfoque de ROLLUP aún podemos usar un ROLLUP tradicional pero con un cambio menor.

En lugar de pasar cada columna al ROLLUP individualmente, pasamos la colección de columnas como un conjunto encerrándolas entre paréntesis. Esto hace que el conjunto de columnas se trate como un solo grupo en lugar de varios grupos. La siguiente consulta le dará los mismos resultados que la anterior:

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

¡Siéntete libre de probar ambos enfoques por ti mismo!
http://sqlfiddle.com/#!4/12ad9/102

Answer 6

Podrías hacer uso de una unión:

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