在 POSTGRES 中返回每行的 COUNT，而不是总和或单行

Question

我对此使用了变体：

SELECT (select count(active) AS true 
        from sooper_entry 
        where active = 't' 
        and entry_id_ref = 28) AS true,
       (select count(active) AS false 
        from sooper_entry 
        where active = 'f' 
        and entry_id_ref = 28) AS false;

因此我可以获得所有 true 和 false 的计数，但我需要在关联数组中返回 true false 计数。

期望的结果：

 true | false | uId 
------+-------+-----
   16 |     0 |  1
   10 |     2 |  3
   13 |    10 |  4
   19 |     8 |  5
   12 |     3 |  8
   21 |     0 | 12
(6 rows)

Answer 1

SELECT 
  sum(case when active = 't' then 1 else 0 end) AS true, 
  sum(case when active = 'f' then 1 else 0 end) AS false, 
  entry_id_ref
FROM sooper_entry
GROUP BY entry_id_ref

Answer 2

SELECT  SUM(active::BOOLEAN::INT) AS active, 
        SUM((NOT active::BOOLEAN)::INT) AS inactive, 
        entry_id_ref
FROM    sooper_entry
GROUP BY
        entry_id_ref