View all aggregations with CUBE
The CUBE operator provides a cross aggregation of all combinations and can be a huge number of rows. This operator works best with non-hierarchical data where you are interested in independent aggregations as well as the combined aggregations.
In this scenario, we wish to find the total number of security incidents in the IncidentRollup table but will not follow a proper hierarchy. Instead, we will focus on aggregating several unrelated attributes.
Este exercício faz parte do curso
Time Series Analysis in SQL Server
Exercício interativo prático
Experimente este exercício completando este código de exemplo.
SELECT
-- Use the ORDER BY clause as a guide for these columns
-- Don't forget that comma after the third column if you
-- copy from the ORDER BY clause!
ir.___,
c.___,
c.___,
SUM(ir.NumberOfIncidents) AS NumberOfIncidents
FROM dbo.IncidentRollup ir
INNER JOIN dbo.Calendar c
ON ir.IncidentDate = c.Date
WHERE
ir.IncidentTypeID IN (3, 4)
GROUP BY
-- GROUP BY should include all non-aggregated columns
ir.___,
c.___,
c.___
-- Fill in your grouping operator
WITH ___
ORDER BY
ir.IncidentTypeID,
c.CalendarQuarterName,
c.WeekOfMonth;