En esta entrada mostrare unos ejemplos con los operadores WITH CUBE y WITH ROLLUP, que junto a la función GROUPING resulta de gran utilidad en muchos casos. Explicare el tratamiento de los nulos (NULL) en las consultas WITH CUBE y WITH ROLLUP, y se incluyen varios ejemplos de WITH CUBE y WITH ROLLUP, para facilitar su uso.

¿Cuántos transacciones tenemos de INGRESO y de SALIDA?

se crea una variable de tipo tabla para ver estos ejemplos.

DECLARE @TablaCubeRollup TABLE (ID INT IDENTITY(1,1) NOT NULL, TipoAccionAlmacen VARCHAR(50), Articulo VARCHAR(100), Cantidad INT)
INSERT INTO @TablaCubeRollup (TipoAccionAlmacen, Articulo, Cantidad)
	SELECT 'INGRESO','ARTICULO A','25'
	UNION ALL SELECT 'INGRESO','ARTICULO A','15'
	UNION ALL SELECT 'INGRESO','ARTICULO B','11'
	UNION ALL SELECT 'INGRESO','ARTICULO C','45'
	UNION ALL SELECT 'INGRESO','ARTICULO D','55'
	UNION ALL SELECT 'INGRESO','ARTICULO A','85'
	UNION ALL SELECT 'INGRESO','ARTICULO B','69'
	UNION ALL SELECT 'INGRESO','ARTICULO C','23'
	UNION ALL SELECT 'INGRESO','ARTICULO C','45'
	UNION ALL SELECT 'INGRESO','ARTICULO D','63'
	UNION ALL SELECT 'INGRESO','ARTICULO F','85'
	UNION ALL SELECT 'INGRESO','ARTICULO A','75'
	UNION ALL SELECT 'INGRESO','ARTICULO B','63'
	UNION ALL SELECT 'INGRESO','ARTICULO A','15'
	UNION ALL SELECT 'SALIDA','ARTICULO B','8'
	UNION ALL SELECT 'SALIDA','ARTICULO C','25'
	UNION ALL SELECT 'SALIDA','ARTICULO D','33'
	UNION ALL SELECT 'SALIDA','ARTICULO A','40'
	UNION ALL SELECT 'SALIDA','ARTICULO B','69'
	UNION ALL SELECT 'SALIDA','ARTICULO C','15'
	UNION ALL SELECT 'SALIDA','ARTICULO C','45'
	UNION ALL SELECT 'SALIDA','ARTICULO D','23'
	UNION ALL SELECT 'SALIDA','ARTICULO F','72'
	UNION ALL SELECT 'SALIDA','ARTICULO A','74'
	UNION ALL SELECT 'SALIDA','ARTICULO B','45'

-- ¿Cuántos transacciones tenemos de INGRESO y de SALIDA?
SELECT TipoAccionAlmacen, COUNT(ID) Cantidad
	FROM @TablaCubeRollup GROUP BY TipoAccionAlmacen

¿Qué artículo es el más usada?

SELECT TOP 1 Articulo, SUM(Cantidad) Suma
	FROM @TablaCubeRollup GROUP BY Articulo
	ORDER BY SUM(Cantidad) DESC

¿Cantidad de cada tipo de transacción en cada artículo?

SELECT  TipoAccionAlmacen, Articulo, SUM(Cantidad) Cantidad
	FROM @TablaCubeRollup GROUP BY TipoAccionAlmacen, Articulo 
	ORDER BY TipoAccionAlmacen

¿Y si quiero la información agrupada de más maneras?

Tenemos WITH CUBE que nos permite crear nuevas dimensiones en nuestras consultas. Cuando usamos esta cláusula es como si estuviésemos haciendo a la vez todos los GROUP BY posibles. Añadamos el WITH CUBE a la sentencia anterior.

SELECT  TipoAccionAlmacen, Articulo, SUM(Cantidad) Cantidad
	FROM @TablaCubeRollup GROUP BY TipoAccionAlmacen, Articulo 
	WITH CUBE ORDER BY TipoAccionAlmacen, Articulo

¿Cómo sabemos cuál de los NULL es un super agregado y cual es un NULL de verdad?

Vamos a insertar una nueva fila en nuestra tabla

Existe la función GROUPING que nos dice cuando nuestro NULL es de verdad y cuando no. Esta función nos devuelve un 1 si el nombre de la columna pasada como parámetro se usa como resumen y un 0 si no es así.

-- Vamos a insertar una nueva fila en nuestra tabla
INSERT INTO @TablaCubeRollup (TipoAccionAlmacen, Articulo, Cantidad)
	SELECT 'INGRESO',NULL,'25'

-- ¿Cómo sabemos cual de los NULL es un super agregado y cual es un NULL de verdad?
SELECT TipoAccionAlmacen,
	Articulo, 'Todos los Articulo' = GROUPING(Articulo), 
	SUM(Cantidad) Cantidad
	FROM @TablaCubeRollup GROUP BY TipoAccionAlmacen, Articulo 
	WITH CUBE ORDER BY TipoAccionAlmacen, Articulo

Mejoramos el aspecto del resultado.

Mezclando estas funciones nuevas con dos funciones conocidas, CASE e ISNULL para darle un aspecto más elegante al resultado.

¿Podemos saberlo todo?

Así es, vamos a ejecutar la consulta que nos devuelve toda la información que podemos pedir.

-- Vamos a insertar una nueva fila en nuestra tabla
INSERT INTO @TablaCubeRollup (TipoAccionAlmacen, Articulo, Cantidad)
	SELECT 'INGRESO',NULL,'25'

-- ¿Podemos saberlo todo?
SELECT	'TipoAccionAlmacen'= CASE 
			WHEN GROUPING(TipoAccionAlmacen)=1 THEN 'TODAS'
			ELSE ISNULL(TipoAccionAlmacen, 'N/D')
		END,
	'Articulo'= CASE 
			WHEN GROUPING(Articulo)=1 THEN 'TODAS'
			ELSE ISNULL(Articulo, 'N/D')
		END,			 
	SUM(Cantidad) Cantidad
	FROM @TablaCubeRollup GROUP BY TipoAccionAlmacen, Articulo

¿Y qué pasa con el ROLLUP?

WITH CUBE genera un conjunto de resultados que muestra agregados para todas las combinaciones de valores de las columnas seleccionadas, WHIT ROLLUP retorna agregados para una jerarquía de valores de las columnas seleccionadas.

-- Vamos a insertar una nueva fila en nuestra tabla
INSERT INTO @TablaCubeRollup (TipoAccionAlmacen, Articulo, Cantidad)
	SELECT 'INGRESO',NULL,'25'

-- ¿Y qué pasa con el ROLLUP?
SELECT	'TipoAccionAlmacen'= CASE 
			WHEN GROUPING(TipoAccionAlmacen)=1 THEN 'TODAS'
			ELSE ISNULL(TipoAccionAlmacen, 'N/D')
		END,
	'Articulo'= CASE 
			WHEN GROUPING(Articulo)=1 THEN 'TODAS'
			ELSE ISNULL(Articulo, 'N/D')
		END,			 
	SUM(Cantidad) Cantidad
	FROM @TablaCubeRollup 
	GROUP BY TipoAccionAlmacen, Articulo
	WITH ROLLUP ORDER BY TipoAccionAlmacen, Articulo

Como hemos visto, las cláusulas CUBE, ROLLUP y GROUPING, pueden sernos de gran ayuda, aunque por supuesto, debemos ser conscientes de que la utilización de las agrupaciones no es suele ser una buena práctica de rendimiento (ojo, hablando en términos generales, en el caso de sistemas puramente transaccionales). En entornos de Reporting y Data Warehouse, aportan una gran riqueza al lenguaje SQL.