Search code examples
sqlsql-servergroup-bywindow-functions

group by dynamic interval with starting and ending point SQL Server


I have a table containing a column DED with numbers that can go from 0 to infinity. I am interested in grouping them starting always in 0 (upper bound as open and lower bound as closed interval) and get the percentage totals

Suppose I have a column with

DED     AMT
0.0004   4
0.0009   1
0.001    2
0.002    1
0.009    4
0.01     5
0.04     6
0.09     3
0.095    1
0.9      3
1        2
100      1
500      1 

so I would want the following intervals:

DED       AMT    PAMT
0-0.01     12    0.3529
0.01-0.02  5     0.1470
0.04-0.05  6     0.1764
0.09-0.1   4     0.1176
0.9-1      3     0.0882
1          2     0.0588

I have tried:

SELECT CAST(DED/.02*.02 AS VARCHAR) +' - '+CAST(DED/.02*.02 +.01 AS VARCHAR)  AS DED, 
SUM(AMT) AS AMT,ISNULL(SUM(AMT)*1.000/NULLIF(SUM(SUM(AMT)) OVER (),0),0) AS PAMT 
FROM MYTABLE 
   WHERE DED/.02*.02<=1 
   GROUP BY DED/.02*.02

Thanks for your help


Solution

  • SELECT
      ROUND(DED, 2, 1)          AS DED_lower,
      ROUND(DED, 2, 1) + 0.01   AS DED_upper,
      SUM(AMT)                  AS SUM_AMT, 
      SUM(AMT) * 1.0
      /
      SUM(AMT) OVER ()          AS PAMT
    FROM
      mytable
    WHERE
      DED <= 1
    GROUP BY
      ROUND(DED, 2, 1)
    

    ROUND(DED, 2, 1) will round down to two decimal places. Giving equal sized bands of 0.01 in size.