Sai Reddy

Sai Reddy

  • 1.8k
  • 212
  • 14.2k

Quartile Number for group

Sep 19 2020 10:27 AM
 Hi, I am trying to get quartile target each member, can you please help me with the logic?
 
 
 
create table sc
(
Name varchar(30),
Score int
)
insert into sc values ('abc',100)
insert into sc values ('xyz',200)
insert into sc values ('xyz2',90)
insert into sc values ('abc2',80)
insert into sc values ('abc3',300)
insert into sc values ('ahz',50)
insert into sc values ('Sai',70)
insert into sc values ('Raj',0)
;with test as (
select Name,Score,row_number() over(order by score) rank from sc
)
select Name,Score,rank,
(
case when rank<=(select count(1)*.25 from test) then 1
when rank<=(select count(1)*.50 from test) then 2
when rank<=(select count(1)*.75 from test) then 3
else 4 end
) Quartile from test
 
 
 
 

Brought to you by:

Answers (3)