Uso de Cube y Rollup para subtotales

Agrupamientos incluyendo Subtotales en Select

Uso de 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

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 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(O.Freight) As ‘Monto Total’
from Orders As O
join Customers As C on O.CustomerID = C.CustomerID
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(O.Freight) As ‘Monto Total’
from Orders As O
join Customers As C on O.CustomerID = C.CustomerID
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.

Subtoitales de los clientes cuyo nombre inicia con la letra A.

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

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.