I have a table with values :
CREATE TABLE grade
(id int, name varchar(2), no int);
INSERT INTO grade
(id, name, no)
VALUES
(1, 'A', 7),
(2, 'B', 6),
(3, 'C', 10),
(4, 'D', 12),
(5, 'E', 15),
(6, 'F', 21),
(8, 'B', 16),
(7, 'F', 18),
(9, 'F', 25);
I need output in arrays to use.. i.e. :
[
['range','A','B','F'],
['0.00 - 4.41', 1, 1, 0],
['4.41 - 8.24', 0 , 1, 1]
...
...
['others', 0, 0, 1]
]
This is what I am tring (Fiddle) :
select range, array_agg(name) as name, array_agg(count) as count
from (
select case
when no between 0.00 and 4.41 then '0.00 - 4.41'
when no between 4.41 and 8.24 then '4.41 - 8.24'
when no between 8.24 and 14.77 then '8.24 - 14.77'
when no between 14.77 and 19.35 then '14.77 - 19.35'
when no between 19.35 and 23.00 then '19.35 - 23.00'
else 'Others' end as range, name, count (*) as count
from grade
WHERE name IN ('A','B','F')
group by range, name
order by name
) t
group by range
Is it possible to get desired output from db query only ? or I have use php to iterate through?
Found a way to archive result (Updated Fidddle):
select range, sum(a) as a_name,
sum(b) as b_name,
sum(f) as f_name,
sum(count) as total
from (
select case
when no between 0.00 and 4.41 then '0.00 - 4.41'
when no between 4.41 and 8.24 then '4.41 - 8.24'
when no between 8.24 and 14.77 then '8.24 - 14.77'
when no between 14.77 and 19.35 then '14.77 - 19.35'
when no between 19.35 and 23.00 then '19.35 - 23.00'
else 'Others' end as range,
count(name = 'A' OR NULL) as A,
count(name = 'B' OR NULL) as B,
count(name = 'F' OR NULL) as F,
count (*) as count
from grade
WHERE name IN ('A','B','F')
group by range, name
order by name
) t
group by range