sql - 在 POSTGRES 中每行返回一个计数而不是总和或单行

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