How to select count of 0s, 1s, and both 0s and 1s in a postgres table column?

Say there’s a table that has columns named binary_value, name, and created_at along with the id column.

Here’s the SQL Fiddle for this question: http://sqlfiddle.com/#!15/d15d1/36

What would be an efficient query to get a result like the following?

ones_count | zeros_count | total
3 | 1 | 4

So far, I’ve got:

with cte2(count_type, counted) as (
  with cte as (
    select binary_value,
      sum(case when binary_value = 1 then 1 else 0 end) as ones_count,
      sum(case when binary_value = 0 then 1 else 0 end) as zeros_count
    from infos
    where name = 'me'
    and created_at >= '2020-03-10 21:13:01.319677'
    and created_at <= '2020-03-10 21:13:01.619677'
    group by binary_value
  )
  select 'ones_count', ones_count from cte where binary_value = 1
  union
  select 'ones_count', zeros_count from cte where binary_value = 0
  union
  select 'total', sum(ones_count + zeros_count) as total from cte
  )
select * from cte2;

Which gives it in column form:

count_type | counted
ones_count | 1
total | 4
ones_count | 3

How can we get the result in a row? Perhaps there’s a different approach altogether than Common Table Expression? I’m starting to look at crosstab, which is postgres-specific, and so wondering if all this is overkill.

Including DDL and data here, too:

create table infos (
  id serial primary key,
  name character varying not null,
  binary_value integer not null,
  created_at timestamp without time zone not null
)

insert into infos ("binary_value", "name", "created_at") values 
(1, 'me', '2020-03-10 21:13:01.319677'),
(1, 'me', '2020-03-10 21:13:01.419677'),
(0, 'me', '2020-03-10 21:13:01.519677'),
(1, 'me', '2020-03-10 21:13:01.619677');

Answer

I think you just want conditional aggregation:

select count(*) filter (where binary_value = 0) as num_0s,
       count(*) filter (where binary_value = 1) as num_1s,
       count(*)
from infos
where name = 'me' and
      created_at >= '2020-03-10 21:13:01.319677' and
      created_at <= '2020-03-10 21:13:01.619677';

The date comparison looks rather, uh, specific. I assume that you really intend a range there.

Here is a SQL Fiddle.

Note: If you are really using Postgres 9.3, then you can’t use the filter clause (alas). Instead:

select sum( (binary_value = 0)::int ) as num_0s,
       sum( (binary_value = 1)::int ) as num_1s,
       count(*)
from infos
where name = 'me' and
      created_at >= '2020-03-10 21:13:01.319677' and
      created_at <= '2020-03-10 21:13:01.619677';

Also, if you wanted the results in three separate rows, a simpler query is:

select binary_value, count(*)
from infos
where name = 'me' and
      created_at >= '2020-03-10 21:13:01.319677' and
      created_at <= '2020-03-10 21:13:01.619677'
group by grouping sets ( (binary_value), () );