Uso de Grouping Sets en SQL Server
Los agrupamiento en SQL Server permiten mostrar un conjunto de resultados donde una o mas columnas que se muestran es el resultado del uso de alguna de las funciones de agregado que calculan suma, promedio,
máximo, mínimo, etc.
Si se desea mostrar mas de un agrupamiento en el mismo conjunto de resultado se deberá usar los Grouping Sets, estos son similares al uso del operador Union All. En este artículo se desea explicar como se usa la opción
Grouping Sets para mostrar múltiples resultados de agrupamientos en la misma instrucción. Adicionalmente se muestra como se usa la función Grouping.
Para mayor información ver
Funciones de agregado
Agrupamientos en SQL Server
Cláusula Union en consultas
Ordenamientos en SQL Server
Generar una tabla usando Select into
Vistas en SQL Server
Usando la base de datos Northwind
use Northwind
go
Usando Union All
El uso de Union all permite unir varias consultas en una agrupando varios resultados en uno solo. Se va a crear cuatro consultas, las que después usando Union All se mostrarán en un sólo conjunto de resultado.
Consulta 1: Unidades totales vendidas por categoría.
Select C.CategoryName As ‘Categoría’,
sum(D.Quantity) As ‘Total’
from [Order Details] As D
join Products As P on D.ProductID = P.ProductID
join Categories As C on P.CategoryID = C.CategoryID
group by C.CategoryName
go
El resultado se muestra en la siguiente imagen
Consulta 2: Ventas totales en unidades por Empleado
Select Empleado = CONCAT_WS(‘ ‘,E.FirstName, E.LastName) ,
sum(D.Quantity) As ‘Total’
from [Order Details] As D
join Products As P on D.ProductID = P.ProductID
join Orders As O on D.OrderID = O.OrderID
join Employees As E on O.EmployeeID = E.EmployeeID
group by CONCAT_WS(‘ ‘,E.FirstName, E.LastName)
go
El resultado se muestra en la siguiente imagen
Consulta 3: Las compras totales en unidades por Cliente
Select C.CompanyName As ‘Cliente’,
sum(D.Quantity) As ‘Total’
from [Order Details] As D
join Products As P on D.ProductID = P.ProductID
join Orders As O on D.OrderID = O.OrderID
join Customers As C on O.CustomerID= C.CustomerID
group by C.CompanyName
go
El resultado se muestra en la siguiente imagen
Consulta 4: Ventas totales
Select sum(D.Quantity) As ‘Total’
from [Order Details] As D
go
El resultado se muestra en la siguiente imagen
Usando Union All
Las instrucciones anteriores podemos mostrarlas en un sólo conjunto de resultados.
Select C.CategoryName As ‘Item’,
sum(D.Quantity) As ‘Total’
from [Order Details] As D
join Products As P on D.ProductID = P.ProductID
join Categories As C on P.CategoryID = C.CategoryID
group by C.CategoryName
Union All
Select CONCAT_WS(‘ ‘,E.FirstName, E.LastName) As ‘Item’,
sum(D.Quantity) As ‘Total’
from [Order Details] As D
join Products As P on D.ProductID = P.ProductID
join Orders As O on D.OrderID = O.OrderID
join Employees As E on O.EmployeeID = E.EmployeeID
group by CONCAT_WS(‘ ‘,E.FirstName, E.LastName)
Union All
Select C.CompanyName As ‘Item’,
sum(D.Quantity) As ‘Total’
from [Order Details] As D
join Products As P on D.ProductID = P.ProductID
join Orders As O on D.OrderID = O.OrderID
join Customers As C on O.CustomerID= C.CustomerID
group by C.CompanyName
Union All
Select ‘Total General’ As ‘Item’, sum(D.Quantity) As ‘Total’
from [Order Details] As D
go
El resultado se muestra en la siguiente imagen
Es importante anotar que posiblemente el resultado no sea adecuado para mostrarlo en un sólo conjunto de resultados. La idea del ejercicio es mostrar el uso de Union All para mostrar varios resultados en un sólo conjunto, lo que se va a explicar en el uso de Grouping Sets.
Usando Grouping Sets
Para el uso de Grouping sets se va a crear una tabla con los datos necesarios. Por cada empleado y por categorías, se va a calcular el total de ventas por año.
Revise y considere la opción de generar una vista, es importante el uso de la vista porque los datos se actualizan a diferencia de la tabla creada que no se actualiza.
Generando la tabla llamada TotalesVentasEmpleadoAñoCategoria.
select
CONCAT_WS(‘ ‘,E.FirstName, E.LastName) As ‘Empleado’,
C.CategoryName As ‘Categoría’,
Year(O.OrderDate) As ‘Año’,
SUM(D.Quantity * D.UnitPrice) As ‘Total’
into TotalesVentasEmpleadoAñoCategoria
from Employees As E
join Orders As O on E.EmployeeID = O.EmployeeID
Join [Order Details] As D on O.OrderID = D.OrderID
join Products As P on D.ProductID = P.ProductID
join Categories As C on P.CategoryID = C.CategoryID
Group by
CONCAT_WS(‘ ‘,E.FirstName, E.LastName),
C.CategoryName, Year(O.OrderDate)
Order by
CONCAT_WS(‘ ‘,E.FirstName, E.LastName),
C.CategoryName, Year(O.OrderDate)
go
Listado de los registros de la tabla creada
select * from TotalesVentasEmpleadoAñoCategoria
go
El resultado se muestra en la siguiente imagen
Para mostrar el uso de grouping sets se van a constriur cuatro diferentes consultas las que se mostrarán de manera individual primero, luego usando Grouping Sets se mostrarán agrupadas.
Consulta 1: Ventas totales por categoría
Select
T.Categoría,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
group by T.Categoría
go
El resultado se muestra en la siguiente imagen
Consulta 2: Total de ventas por empleado.
Select
T.Empleado,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
group by T.Empleado
go
El resultado se muestra en la siguiente imagen
Consulta 3: Total de ventas por empleado y por categoría.
Select
T.Empleado, T.Categoría,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
group by T.Empleado, T.Categoría
go
El resultado se muestra en la siguiente imagen
Consulta 4: Total de ventas general
Select
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
go
El resultado se muestra en la siguiente imagen
Note que en las cuatro consultas anteriores se ha especificado agrupamientos, estos cuatro agrupamientos son:
T.Categoría
T.Empleado
T.Empleado, T.Categoría
La última no tiene Group by porque no existe campo en el listado sin función de agregado.
Usando el operador Union All
Select
T.Categoría As ‘Categoría’,
» As Empleado,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
group by T.Categoría
Union All
Select » As ‘Categoría’,
T.Empleado As ‘Empleado’,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
group by T.Empleado
Union All
Select
T.Categoría As ‘Categoría’,
T.Empleado As ‘Empleado’,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
group by T.Categoría, T.Empleado
Union All
Select
» As ‘Categoría’,
» As ‘Empleado’,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
go
El resultado se muestra en la siguiente imagen
La misma instrucción usando Grouping Sets
Select isnull(T.Categoría,») As ‘Categoría’,
isnull(T.Empleado,») As ‘Empleado’,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
Group by
Grouping Sets
(
(T.Categoría),
(T.Empleado),
(T.Categoría, T.Empleado),
()
)
Order by T.Categoría, T.Empleado
go
El resultado se muestra en la siguiente imagen
Usando la función Grouping.
Se han incluido dos columnas donde se usa la función Grouping.
Select Grouping(T.Categoría) As ‘Agrupado por Categoría’,
Grouping(T.Empleado) As ‘Agrupado por Empleado’,
isnull(T.Categoría,») As ‘Categoría’,
isnull(T.Empleado,») As ‘Empleado’,
Sum(T.Total) As ‘Total’
from TotalesVentasEmpleadoAñoCategoria As T
Group by
Grouping Sets
(
(T.Categoría),
(T.Empleado),
(T.Categoría, T.Empleado),
()
)
Order by T.Categoría, T.Empleado
go
El resultado se muestra en la siguiente imagen
Explicación
Al usar la función Grouping en un campo muestra dos posibles valores, CERO y UNO. El valor de UNO (1) significa que se ha agrupado por el campo que se especifica como parámetro de la función, el valor de CERO (0) indica que no se ha agrupado por el campo dentro de los paréntesis de la función.
Note en la imagen anterior que el primer registro que muestra las ventas totales tanto por empleado y por categoría aparece en UNO en ambas columnas. De los registros 2 hasta el 10 se ha agrupado por categoría, mostrando las ventas totales de cada empleado. El registro 11 muestra el total de la categoría Beverage, significa la agrupación de las ventas de todos los empleados
en esa categoría.
A partir del registro 12, muestra el total de las ventas tanto por Categoría como por Empleado, presenta CERO en ambas columnas porque el agrupamiento es por los dos campos y no por cada uno de ellos.