I have a task to find quartiles value from this data:
table's name : student
Here is the sql:
select p.major AS ProgramStudi,
count(usia) as N, AVG(usia) as Mean,
MIN(usia) as Minimum, MAX(usia) as Maximum,
STDDEV(usia) as stddev,
max(case when floor(0.25*pp.cnt) = rn then usia end) as quartile_1,
max(case when floor(0.50*pp.cnt) = rn then usia end) as quartile_2,
max(case when floor(0.75*pp.cnt) = rn then usia end) as quartile_3
from (select p.*,
(@rn := if(@m = p.major, @rn + 1,
if(@m := p.major, 1, 1)
)
) as rn
from student p cross join
(select @m := '', @rn := 0) params
order by p.major, p.usia
) p join
(select p.major, count(*) as cnt
from student p
group by p.major
) pp
on p.major = pp.major
group by p.major;
and the result goes like this:
Why can there be zero values on quartiles? What may I do to fix the zero values? Thanks in advance


