Get startedGet started for free

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

View Course

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;
Edit and Run Code