Summarize data over a time frame
There are several useful aggregate functions in SQL Server which we can use to summarize our data over time frames and gain insights. In the following example, you will look at a set of incident reports at a fictional company. They have already rolled up their incidents to the daily grain, giving us a number of incidents per type and day. We would like to investigate further and review incidents over a three-month period, from August 1 through October 31st, and gain basic insights through aggregation.
The key aggregate functions we will use are COUNT(), SUM(), MIN(), and MAX(). In the next exercise, we will look at some of the statistical aggregate functions.
This exercise is part of the course
Time Series Analysis in SQL Server
Exercise instructions
- Fill in the appropriate aggregate function based on the column name. Choose from
COUNT(),SUM(),MIN(), andMAX()for each.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Fill in the appropriate aggregate functions
SELECT
it.IncidentType,
___(1) AS NumberOfRows,
___(ir.NumberOfIncidents) AS TotalNumberOfIncidents,
___(ir.NumberOfIncidents) AS MinNumberOfIncidents,
___(ir.NumberOfIncidents) AS MaxNumberOfIncidents,
___(ir.IncidentDate) As MinIncidentDate,
___(ir.IncidentDate) AS MaxIncidentDate
FROM dbo.IncidentRollup ir
INNER JOIN dbo.IncidentType it
ON ir.IncidentTypeID = it.IncidentTypeID
WHERE
ir.IncidentDate BETWEEN '2019-08-01' AND '2019-10-31'
GROUP BY
it.IncidentType;