postgresql — Postgres для php Array

Question

postgresql — Postgres для php Array

У меня есть таблица со значениями:

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);

Мне нужен вывод в массивах, чтобы использовать .. т.е.

[
['range','A','B','F'],
['0.00 - 4.41', 1, 1, 0],
['4.41 - 8.24', 0 , 1, 1]
...
...
['others', 0, 0, 1]
]

Это то, что я провожу (скрипка):

    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

Можно ли получить желаемый результат только из запроса базы данных? или я использую php для перебора?

2

php postgresql

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Нашел способ архивировать результат (Обновленная скрипка):

    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

0