sqlsql-servergroup-byquartile

Calculating LQ, UQ and IQR in SQL for grouped data


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


Solution

  • 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;
    

    db<>fiddle