current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 13 down vote favorite
1
 
myCol
------
 true
 true
 true
 false
 false
 null

In the above table, if I do :

select count(*), count(myCol);

I get 6, 5

I get 5 as it doesn't count the null entry.

How do I also count the number of true values (3 in the example)?

(This is a simplification and I'm actually using a much more complicated expression within the count function)

Edit summary: I also want to include a plain count(*) in the query, so can't use a where clause

share|improve this question
    
Does 't' stand for True anf 'f' for False? Or are you looking for something like SELECT COUNT(DISTINCT myCol). –  Shamit Verma Mar 22 '11 at 19:16
    
take a look at my second example, you can throw a WHERE myCol = true in there if you want and if you remove the first *, it'll just return the number. –  vol7ron Mar 22 '11 at 19:41
    
@Shamit yes t stands for true, and f stands for false, I've updated the question –  EoghanM Mar 22 '11 at 20:23
    
You might as well not simplify your question/query... your requirements restrict the better performance possibilities and people are responding with inefficient answers, which are getting bumped up for no good reason. –  vol7ron Mar 23 '11 at 0:18
1  
@vol7ron in my defense there has to be some simplification in order to ask a comprehensible question, but yes, I oversimplified when I originally posted. –  EoghanM Mar 24 '11 at 17:26

6 Answers 6

up vote 27 down vote accepted 
SELECT COALESCE(sum(CASE WHEN myCol THEN 1 ELSE 0 END),0) FROM <table name>

or, as you found out for yourself:

SELECT count(CASE WHEN myCol THEN 1 ELSE null END) FROM <table name>
share|improve this answer
    
This is a good hack and gets the right answer from me. I'll accept it unless someone comes up with a shorter solution? –  EoghanM Mar 22 '11 at 20:24
1  
also, any reason why you did sum(.. THEN 1 ELSE 0) instead of count(.. THEN true else null) ? –  EoghanM Mar 22 '11 at 20:32
2  
No... it is just that I wasn't sure which values would count() count... and I knew that sum did the trick. But beware: On second thought I believe that sum() over only null values will return null, so it should be COALESCE(sum(...),0) for you, or, in other words, count() is better, –  Daniel Mar 22 '11 at 21:38
    
Thankyou! Never thought my query would work in a million years! –  user1158559 Dec 4 '12 at 18:15
    
@EoghanM, see shorter answer involving cast. –  Dwayne Towell Jan 3 '14 at 18:10
up vote 13 down vote

probably, the best approach is to use nullif function.

in general

select
    count(nullif(myCol = false, true)),  -- count false values
    count(nullif(myCol = true, true)),   -- count true values
    count(myCol);

or in short

select
    count(nullif(myCol, true)),  -- count false values
    count(nullif(myCol, false)), -- count true values
    count(myCol);

http://www.postgresql.org/docs/9.0/static/functions-conditional.html

share|improve this answer
up vote 7 down vote 
select f1,
       CASE WHEN f1 = 't' THEN COUNT(*) 
            WHEN f1 = 'f' THEN COUNT(*) 
            END AS counts,
       (SELECT COUNT(*) FROM mytable) AS total_counts
from mytable
group by f1

Or Maybe this

SELECT SUM(CASE WHEN f1 = 't' THEN 1 END) AS t,
       SUM(CASE WHEN f1 = 'f' THEN 1 END) AS f,
       SUM(CASE WHEN f1 NOT IN ('t','f') OR f1 IS NULL THEN 1 END) AS others,
       SUM(CASE WHEN f1 IS NOT NULL OR f1 IS NULL THEN 1 ELSE 0 END) AS total_count
FROM mytable;
share|improve this answer
    
+1 If myCol expression is a boolean, you can replace the check with where (myCol) –  ypercube Mar 22 '11 at 19:14
    
sorry I oversimplified my example: I can't use a where clause as I also want to return a total count representing the total number of rows, as well as a count of the true values. –  EoghanM Mar 22 '11 at 20:20
up vote 2 down vote 
SELECT count(*)         -- or count(myCol)
FROM   <table name>     -- replace <table name> with your table
WHERE  myCol = true;

Here's a way with Windowing Function:

SELECT DISTINCT *, count(*) over(partition by myCol)
FROM   <table name>;

-- Outputs:
-- --------------
-- myCol | count
-- ------+-------
--  f    |  2
--  t    |  3
--       |  1
share|improve this answer
    
sorry I can't return multiple rows for the more complicated example I'm applying this solution to. –  EoghanM Mar 22 '11 at 20:33
    
Yes, but you can restrict it further by just adding WHERE myCol = true. I provided the second example not because it's any faster, but more as an educational piece to Postgres's windowing functions, which many users aren't comfortable with, or don't know about. –  vol7ron Mar 23 '11 at 0:00
up vote 2 down vote

In MySQL, you can do this as well:

SELECT count(*) AS total
     , sum(myCol) AS countTrue --yes, you can add TRUEs as TRUE=1 and FALSE=0 !!
FROM yourTable
;

I think that in Postgres, this works:

SELECT count(*) AS total
     , sum(myCol::int) AS countTrue --convert Boolean to Integer
FROM yourTable
;

or better (to avoid :: and use standard SQL syntax):

SELECT count(*) AS total
     , sum(CAST(myCol AS int)) AS countTrue --convert Boolean to Integer
FROM yourTable
;
share|improve this answer
up vote 2 down vote

Cast the Boolean to an integer and sum.

SELECT count(*),sum(myCol::int);

You get 6,3.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.