Get Interval Percentage

Question

Please consider this Table:

FileName            FileSize
----------------------------
 1                   33
 2                   198
 3                   10
 4                   127
 5                   85
 6                   23
 7                   105
 8                   158
 9                   78
 10                  90

and I want to create such this result:

FileSize             Percentage
--------------------------------
1-50                    30%
50-150                  50%
150-200                 20%

How I can group by and create percentage based on intervals?

Thanks

Answer 1

For a query without bounds table you can create a table inline using VALUES constructor like below

select DISTINCT
CAST(I.LowNumber as VARCHAR) + ' - '+  CAST(I.HighNumber as VARCHAR) as FileSize,
COUNT(*) OVER (PARTITION BY lowNumber,HighNumber ORDER By lowNumber) * 100.00 /COUNT(*) OVER ( ORDER BY (SELECT 1)) as percentage
from TblFile F
join (values (1, 50),(50, 150),(150, 200)) as I(LowNumber, highNumber)
on F.FileSize >=I.LowNumber and F.FileSize<I.HighNumber
Order By I.LowNumber

Your query should look like

select DISTINCT
CAST(I.LowNumber as VARCHAR) + ' - '+  CAST(I.HighNumber as VARCHAR) as FileSize,
COUNT(*) OVER (PARTITION BY lowNumber,HighNumber ORDER By lowNumber) * 100.00 /COUNT(*) OVER ( ORDER BY (SELECT 1)) as percentage
from TblFile F
join TblInterval I 
on F.FileSize >=I.LowNumber and F.FileSize<I.HighNumber

Explanation:

Ideally you should leverage set based approach and store the range values in a table. This allows for faster processing and also allows you a single place to limits externally and not in the procedure. This is also in line with Dependency injection principle.

For inline anonymous tables use VALUES constructor. More on this at this msdn link

PS: Insert scripts for table

--create table tblInterval (LowNumber Int, HighNumber Int)
--insert into tblInterval values
--(1,50),(50,150),(150,200)

create table tblFile (fileName int,fileSize int)
insert into tblFile values
( 1 ,33)
,( 2 ,198 )
,( 3 ,10  )
,( 4 ,127 )
,( 5 ,85  )
,( 6 ,23  )
,( 7 ,105 )
,( 8 ,158 )
,( 9 ,78  )
,( 10,90  )

Assuming you have a table like below

TblInterval

LowNumber HighNumber
  1        50
 50       150
150       200