Working with statistical aggregate functions
SQL Server offers several aggregate functions for statistical purpose. The AVG() function generates the mean of a sample. STDEV() and STDEVP() give us the standard deviation of a sample and of a population, respectively. VAR() and VARP() give us the variance of a sample and a population, respectively. These are in addition to the aggregate functions we learned about in the previous exercise, including SUM(), COUNT(), MIN(), and MAX().
In this exercise, we will look once more at incident rollup and incident type data, this time for quarter 2 of calendar year 2020. We would like to get an idea of how much spread there is in incident occurrence--that is, if we see a consistent number of incidents on a daily basis or if we see wider swings.
This exercise is part of the course
Time Series Analysis in SQL Server
Exercise instructions
- Fill in the missing aggregate functions. For standard deviation and variance, use the sample functions rather than population functions.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Fill in the missing function names
SELECT
it.IncidentType,
___(ir.NumberOfIncidents) AS MeanNumberOfIncidents,
___(CAST(ir.NumberOfIncidents AS DECIMAL(4,2))) AS MeanNumberOfIncidents,
___(ir.NumberOfIncidents) AS NumberOfIncidentsStandardDeviation,
___(ir.NumberOfIncidents) AS NumberOfIncidentsVariance,
___(1) AS NumberOfRows
FROM dbo.IncidentRollup ir
INNER JOIN dbo.IncidentType it
ON ir.IncidentTypeID = it.IncidentTypeID
INNER JOIN dbo.Calendar c
ON ir.IncidentDate = c.Date
WHERE
c.CalendarQuarter = 2
AND c.CalendarYear = 2020
GROUP BY
it.IncidentType;