开发者

RETURN a COUNT each row instead of a sum or single row in POSTGRES

开发者 https://www.devze.com 2023-03-23 06:22 出处:网络
I have used variations on this: SELECT (select count(active) AS true from sooper_entry where active = \'t\'

I have used variations on this:

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;

So I can get a COUNT of all the true and false, but I need a true false count returned in an associative array.

desired result:

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


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


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
0

精彩评论

暂无评论...
验证码 换一张
取 消