Counting total and true condition lines

mysql, postgresql, sql

Solution

select count(*) as total_lines, count(a = 1 or null) as condition_true
from t
;
 total_lines | condition_true 
-------------+----------------
           3 |              1

It works because:

First while `count(*)` counts all lines regardless of anything, `count(my_column)` will count only those lines where my_column is not null:

select count(a) as total
from t
;
 total 
-------
     2

Second `(false or null)` returns `null` so whenever my condition is not met it will return `null` and will not be counted by `count(condition or null)` which only counts not nulls.

Problem

How to count the number of lines in a table and the number of lines where a certain condition is true without resorting to subselects like this: ``` create table t (a integer); insert into t (a) values (1), (2), (null); select (select count(*) from t) as total_lines, (select count(*) from t where a = 1) as condition_true ; total_lines | condition_true -------------+---------------- 3 | 1 ```

Original source