postgresql - sql - count of true values

Question

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

User · Answer

SELECT count            -- or count myCol  FROM    lt table name gt      -- replace  lt table name gt  with your table WHERE  myCol   true    Here s a way with Windowing Function   SELECT DISTINCT    count    over partition by myCol  FROM    lt table name gt    -- Outputs  -- -------------- -- myCol   count -- ------ ------- --  f       2 --  t       3 --          1

User · Answer

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  lt  7 OR NULL     FROM GENERATE SERIES 0 10  t x     gives the same result than  SELECT SUM CASE WHEN x  lt  7 THEN 1 ELSE 0 END     FROM GENERATE SERIES 0 10  t x

User · Answer

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

User · Answer

select count myCol  from mytable group by myCol    will group the 3 possible states of bool  false  true  0  in three rows especially handy when grouping together with another column like day

User · Answer

Cast the Boolean to an integer and sum   SELECT count    sum myCol  int     You get 6 3

User · Answer

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

User · Answer

SELECT COALESCE sum CASE WHEN myCol THEN 1 ELSE 0 END  0  FROM  lt table name gt    or  as you found out for yourself   SELECT count CASE WHEN myCol THEN 1 END  FROM  lt table name gt

User · Answer

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

User · Answer

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

User · Answer

Simply convert boolean field to integer and do a sum  This will work on postgresql    select sum myCol  int  from  lt table name gt    Hope that helps

[sql] postgresql - sql - count of `true` values

Examples related to sql

Examples related to postgresql