PostgreSQL: Make data appear in different rows instead of same row

I have this query:

SELECT
    SUM(CASE WHEN color = 'blue' THEN 1 ELSE 0 END) AS "Blue",
    SUM(CASE WHEN color = 'purple' THEN 1 ELSE 0 END) AS "Purple",
    SUM(CASE WHEN color = 'yellow' THEN 1 ELSE 0 END) AS "Yellow",
    SUM(CASE WHEN color= 'blue' THEN 1 WHEN color= 'yellow' THEN -1 END) AS "Mixed"
FROM table;

And the result gives me a table with 4 columns and 1 row:

e.g. Blue | Purple | Yellow | Mixed

But I want the results to appear in 4 rows and 1 column like this:

Blue

• Blue
• Purple
• Yellow
• Mixed

Does anyone have any idea what I could do in this case? I’ve been working on this forever.

Answer

This is a bit of a pain, because of the mixed value — a simple aggregation is not sufficient. So, use a lateral join to unpivot:

SELECT v.*
FROM (SELECT SUM(CASE WHEN color = 'blue' THEN 1 ELSE 0 END) AS Blue,
             SUM(CASE WHEN color = 'purple' THEN 1 ELSE 0 END) AS Purple,
             SUM(CASE WHEN color = 'yellow' THEN 1 ELSE 0 END) AS Yellow,
             SUM(CASE WHEN color = 'blue' THEN 1 WHEN color = 'yellow' THEN -1 END) AS Mixed
      FROM t
     ) t CROSS JOIN LATERAL
     (VALUES ('Blue', blue), ('Purple', purple), ('Yellow', yellow), ('Mixed', mixed)
     ) v(which, val);

Actually, in Postgres, you could also write this as:

SELECT v.*
FROM (SELECT COUNT(*) FILTER (WHERE color = 'blue') AS Blue,
             COUNT(*) FILTER (WHERE color = 'purple') AS Purple,
             COUNT(*) FILTER (WHERE color = 'yellow') AS Yellow
      FROM t
     ) t CROSS JOIN LATERAL
     (VALUES ('Blue', blue), ('Purple', purple), ('Yellow', yellow), ('Mixed', blue - yellow)
     ) v(which, val)