Uso de Cube y Rollup

Como hacer agrupamientos incluyendo Subtotales en SQL Server

Como usar Cube o Rollup

Los operadores Cube y Rollup son extensiones de la cláusula Group By (Ver Agrupamientos), permiten calcular subtotales de acuerdo a las agrupaciones especificadas en la consulta.

Importante:
Para que las opciones funcionen, el nivel de compatibilidad debe ser al menos 100,  correspondiente a SQL Server 2008. (Ver Niveles de compatibilidad)

Usando Northwind
Cambiar la Compatibilidad de la BD a 100

USE master
GO
ALTER DATABASE Northwind SET COMPATIBILITY_LEVEL = 100
GO
use Northwind
go

Ejercicios

1.  –Listar los Clientes, la cantidad de órdenes por año y el monto total de las órdenes (Valores sumados)
Incluir el total por Año (use Cube)

select
C.CompanyName As ‘Cliente’, YEAR(O.OrderDate) As ‘Año’,
Count(O.OrderID) As ‘Cantidad de Órdenes’,
Sum(D.Quantity * D.UnitPrice) As ‘Monto Total’
from Orders As O
join Customers As C on O.CustomerID = C.CustomerID
join [Order Details] as D on O.OrderID = D.OrderID
Group by Cube(C.CompanyName,YEAR(O.OrderDate))
go

Para poder visualizar los resultados, mostramos los clientes cuyo nombre inicia con la letra A

select
C.CompanyName As ‘Cliente’, YEAR(O.OrderDate) As ‘Año’,
Count(O.OrderID) As ‘Cantidad de Órdenes’,
Sum(D.Quantity * D.UnitPrice) As ‘Monto Total’
from Orders As O
join Customers As C on O.CustomerID = C.CustomerID
join [Order Details] as D on O.OrderID = D.OrderID
where C.CompanyName like ‘A%’
Group by Cube(C.CompanyName,YEAR(O.OrderDate))
go

Los resultados se muestran en la siguiente imagen, note que después de cada año se incluye un total y al final se incluye un total general.

 




2. — Listar los empleados (nombre completo), el Año y la cantidad de órdenes por año

select Empleado = E.LastName+ ‘ ‘ + E.FirstName, YEAR(O.OrderDate) As Año,
Count(O.Orderid) As ‘Cantidad de Órdenes’ from Orders As O
join Employees As E
on O.EmployeeID = E.EmployeeID
Group By Cube(E.LastName+ ‘ ‘ + E.FirstName, YEAR(O.OrderDate))
go

En la figura se muestran los datos de los años 1996 y 1997, además de los totales incluidos al usar Cube.

3. –Listar la cantidad de órdenes por año e incluir el total general al finalizar.

select Year(OrderDate) As ‘Año’, Count(OrderID) As ‘Cantidad de Órdenes’
from orders Group By Year(OrderDate)
with rollup
go

4. — Listar los Empleados y la cantidad de órdenes y monto total por año, mostrar los totales por Empleado

select Empleado = E.LastName+ ‘ ‘ + E.FirstName, YEAR(O.OrderDate) As ‘Año’,
Count(O.Orderid) As ‘Cantidad de Órdenes’ from Orders As O
join Employees As E
on O.EmployeeID = E.EmployeeID
Group By E.LastName+ ‘ ‘ + E.FirstName, YEAR(O.OrderDate)
with rollup
go

La imagen muestra parte del resultado con los tres primeros empleados.