I'm struggling to work out how I would get the upper/lower quartiles and IQR for grouped data in SQL. Say I have some data similar to below:
ID Data
1 21
1 37
1 86
1 1
1 34
1 77
2 5
2 15
2 29
3 12
3 76
3 54
3 10
I would like to be able to get the LQ, UQ and IQR from the Data column for each ID, as below:
ID LQ UQ IQR
1 24.25 67.0 42.75
2 10.00 22.0 12.00
3 11.50 59.5 48.00
So far everything i've found doesn't look at the grouping bit. I know PERCENTILE_COUNT can be used to find which quartile each value falls in to, but I'm struggling to put it all together
Indeed, you can use PERCENTILE_CONT
to get this information. Then you do a simple grouping
SELECT
ID,
LQ,
UQ,
IQR = UQ - LQ
FROM (
SELECT
ID,
LQ = PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Data) OVER (PARTITION BY ID),
UQ = PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Data) OVER (PARTITION BY ID)
FROM YourTable t
) t
GROUP BY ID, LQ, UQ;