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

Question

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)

marc · Accepted Answer · 2011-07-25 15:28:22Z

4

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

edited Jul 25, 2011 at 15:28

answered Jul 25, 2011 at 15:19

marc

6,2431 gold badge31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Quassnoi Over a year ago

This will return equal counts.

roberthuttinger Over a year ago

Thanks! each row returns 14 | 3 (example)

roberthuttinger Over a year ago

Thanks (@quassnoi as well), you both pointed out my glaring omission of GROUP BY! cheers!

roberthuttinger Over a year ago

is there a benfit ti using the cast as opposed to case?

Quassnoi · Accepted Answer · 2011-07-25 15:35:51Z

1

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

edited Jul 25, 2011 at 15:35

answered Jul 25, 2011 at 15:21

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

4 Comments

roberthuttinger Over a year ago

Perfection and simplicity! Thanks much, you do rock! (apparently I cannot +1 anyone yet, someone please do it for me!) Yay I can +1 now!

Quassnoi Over a year ago

@robert: please mark one of the answers (that you think helped you most) as accepted. To do this, click the check mark under the number of votes for the answer so that it becomes green.

roberthuttinger Over a year ago

One slight addition from me:

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

Quassnoi Over a year ago

@robert: if active is a boolean, you may omit the ::BOOLEAN part (it's a cast to boolean)

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related