postgresql - sql - count of `true` values

postgresql - sql - count of `true` values

---

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

---

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 END) FROM <table name>

---

Cast the Boolean to an integer and sum.

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

You get 6,3.

---

Since PostgreSQL 9.4 there's the FILTER clause, which allows for a very concise query to count the true values:

select count(*) filter (where myCol)
from tbl;

The above query is a bad example in that a simple WHERE clause would suffice, and is for demonstrating the syntax only. Where the FILTER clause shines is that it is easy to combine with other aggregates:

select count(*), -- all
       count(myCol), -- non null
       count(*) filter (where myCol) -- true
from tbl;

The clause is especially handy for aggregates on a column that uses another column as the predicate, while allowing to fetch differently filtered aggregates in a single query:

select count(*),
       sum(otherCol) filter (where myCol)
from tbl;

---

probably, the best approach is to use nullif function.

in general

select
    count(nullif(myCol = false, true)),  -- count true values
    count(nullif(myCol = true, true)),   -- count false 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

---

The shortest and laziest (without casting) solution would be to use the formula:

SELECT COUNT(myCol OR NULL) FROM myTable;

Try it yourself:

SELECT COUNT(x < 7 OR NULL)
   FROM GENERATE_SERIES(0,10) t(x);

gives the same result than

SELECT SUM(CASE WHEN x < 7 THEN 1 ELSE 0 END)
   FROM GENERATE_SERIES(0,10) t(x);

---

Simply convert boolean field to integer and do a sum. This will work on postgresql :

select sum(myCol::int) from <table name>

Hope that helps!

---

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;

---

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
;

---

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

참고URL : https://stackoverflow.com/questions/5396498/postgresql-sql-count-of-true-values