You could perform a dynamic query. The best option is to use a programming language, but you can also use the sql language.
I made a reduced example:
Example data:
CREATE TABLE t (
answer int
);
INSERT INTO t VALUES (0);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);
INSERT INTO t VALUES (4);
INSERT INTO t VALUES (3);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (2);
Dynamic query:
(SELECT 'SELECT ')
UNION ALL
(SELECT
'SUM(CASE WHEN answer = ' || answer || ' THEN 1 ELSE 0 END) AS res' || answer || ','
FROM t
GROUP BY answer
ORDER BY answer)
UNION ALL
(SELECT 'COUNT(*) AS info2,
SUM(CASE WHEN answer IS NOT NULL THEN 1 ELSE 0 END) AS info3,
SUM(CASE WHEN answer IS NULL THEN 1 ELSE 0 END) AS info4
FROM t;')
The output is your desire query:
SELECT
SUM(CASE WHEN answer = 0 THEN 1 ELSE 0 END) AS res0,
SUM(CASE WHEN answer = 1 THEN 1 ELSE 0 END) AS res1,
SUM(CASE WHEN answer = 2 THEN 1 ELSE 0 END) AS res2,
SUM(CASE WHEN answer = 3 THEN 1 ELSE 0 END) AS res3,
SUM(CASE WHEN answer = 4 THEN 1 ELSE 0 END) AS res4,
COUNT(*) AS info2,
SUM(CASE WHEN answer IS NOT NULL THEN 1 ELSE 0 END) AS info3,
SUM(CASE WHEN answer IS NULL THEN 1 ELSE 0 END) AS info4
FROM t;
Query result (result of execute your desire query):
res0 |
res1 |
res2 |
res3 |
res4 |
info2 |
info3 |
info4 |
1 |
1 |
3 |
2 |
1 |
8 |
8 |
0 |
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…