A script to demo the PERCENTILE_CONT and PERCENTILE_DISC functions in SQL Server 2012.
— Begin Script
CREATE TABLE TestTable2 (
RecordId INT NOT NULL IDENTITY(1, 1),
GroupId INT NOT NULL,
TotalAmount INT NOT NULL
)
GO
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (1, 1)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (1, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (2, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (2, 3)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (2, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (3, 1)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (3, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (4, 3)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (4, 5)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (5, 5)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (5, 6)
GO
SELECT GroupId,
SUM(TotalAmount) as TotalAmountSum,
COUNT(GroupId) as GroupRecordCount
FROM TestTable2
GROUP BY GroupId
SELECT DISTINCT GroupId,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY TotalAmount) OVER (PARTITION BY GroupId) AS PercentContValue,
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY TotalAmount) OVER (PARTITION BY GroupId) AS PercentDiscValue
FROM TestTable2
GO
— End Script
— Results
| Group Id | Total Amount Sum | Group Record Count |
| 1 | 3 | 2 |
| 2 | 7 | 3 |
| 3 | 3 | 2 |
| 4 | 8 | 2 |
| 5 | 11 | 2 |
| Group Id | PercentContValue | PercentDiscValue |
| 1 | 1.5 | 1 |
| 2 | 2 | 2 |
| 3 | 1.5 | 1 |
| 4 | 4 | 3 |
| 5 | 5.5 | 5 |
PERCENTILE_CONT – From BOL: ‘Calculates a percentile based on a continuous distribution of the column value ‘
PERCENTILE_DISC – From BOL: ‘Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset’
The input for both functions is a percent, from 0.0 to 1.0. PERCENTILE_CONT will calculate the value to match the designated percentage for each group. So in this example, using 0.5 will compute the median value.
PERCENTILE_DISC will return an actual value from the group, not a calculated value. If a value from the group doesn’t match the ‘specific percentile’ exactly (if there are an even number of values) the lower value will be returned.
SQL Rob 