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