Postgres到php Array

Question

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?

Answer 1

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