Calculating median in SQL Server
There is no MEDIAN() function in SQL Server. The closest we have is PERCENTILE_CONT(), which finds the value at the nth percentile across a data set.
We would like to figure out how far the median differs from the mean by incident type in our incident rollup set. To do so, we can compare the AVG() function from the prior exercise to PERCENTILE_CONT(). These are window functions, which we will cover in more detail in chapter 4. For now, know that PERCENTILE_CONT() takes a parameter, the percentile (a decimal ranging from from 0. to 1.). The percentile must be within an ordered group inside the WITHIN GROUP clause and OVER a certain range if you need to partition the data. In the WITHIN GROUP section, we need to order by the column whose 50th percentile we want.
This is a part of the course
“Time Series Analysis in SQL Server”
Exercise instructions
- Fill in the missing value for
PERCENTILE_CONT(). - Inside the
WITHIN GROUP()clause, order by number of incidents descending. - In the
OVER()clause, partition byIncidentType(the actual text value, not the ID).
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT DISTINCT
it.IncidentType,
AVG(CAST(ir.NumberOfIncidents AS DECIMAL(4,2)))
OVER(PARTITION BY it.IncidentType) AS MeanNumberOfIncidents,
--- Fill in the missing value
PERCENTILE_CONT(___)
-- Inside our group, order by number of incidents DESC
WITHIN GROUP (ORDER BY ir.___ DESC)
-- Do this for each IncidentType value
OVER (PARTITION BY it.___) AS MedianNumberOfIncidents,
COUNT(1) OVER (PARTITION BY it.IncidentType) 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;This exercise is part of the course
Time Series Analysis in SQL Server
Explore ways to work with date and time data in SQL Server for time series analysis
In this chapter, we will learn techniques to aggregate data over time. We will briefly review aggregation functions and statistical aggregation functions. We will cover upsampling and downsampling of data. Finally, we will look at the grouping operators.
Exercise 1: Basic aggregate functionsExercise 2: Summarize data over a time frameExercise 3: Calculating distinct countsExercise 4: Calculating filtered aggregatesExercise 5: Statistical aggregate functionsExercise 6: Working with statistical aggregate functionsExercise 7: Calculating median in SQL ServerExercise 8: Downsampling and upsampling dataExercise 9: Downsample to a daily grainExercise 10: Downsample to a weekly grainExercise 11: Downsample using a calendar tableExercise 12: Grouping by ROLLUP, CUBE, and GROUPING SETSExercise 13: Generate a summary with ROLLUPExercise 14: View all aggregations with CUBEExercise 15: Generate custom groupings with GROUPING SETSExercise 16: Combine multiple aggregations in one queryWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.