这有点痛苦，因为混合值——简单的聚合是不够的。因此，使用横向连接取消Pivot：

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

实际上，在postgres中，你也可以这样写：

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)

对于前3个计数器，您可以 group by color 集料与使用 UNION ALL 为了 Mixed :

SELECT color, COUNT(*) counter
WHERE color IN ('blue', 'purple', 'yellow') -- remove this line if these are all the possible colors
FROM table
GROUP BY color
UNION ALL
SELECT 'Mixed', SUM(CASE WHEN color = 'blue' THEN 1 WHEN color = 'yellow' THEN -1 END)
WHERE color IN ('blue', 'yellow')
FROM table

此查询将返回2列。
如果您不想要第1列，那么：

SELECT COUNT(*) counter
WHERE color IN ('blue', 'purple', 'yellow') -- remove this line if these are all the possible colors
FROM table
GROUP BY color
UNION ALL
SELECT SUM(CASE WHEN color = 'blue' THEN 1 WHEN color = 'yellow' THEN -1 END)
FROM table
WHERE color IN ('blue', 'yellow')