Extracting counts of booleans by year and months-CodePudding

I have a table like this:

id   timestamp     alive
 1   2019-10-10    true
 2   2019-10-10    false
...  .........    .....
 N   2021-01-01    boolean

What I need:

Extract by year and month how many true and false and the relation false/true the table has. Like this:

 Period    True  False  false/true
 JAN-2019  1000   10      0.01

What I tried:

I did some cte using date_trunc but I got so many fails that I gave up.

CodePudding user response：

You can use the following:

with agg as (
    select cnv."Period"
          ,sum(case when     alive then 1        end) as "True"
          ,sum(case when not alive then 1 else 0 end) as "False"
    from (values                           -- vvvvvvvvvvvvvvvvvvvv
         (1,'2019-10-10'::date, true ) -- replace this values()
        ,(2,'2019-10-10'      , false) -- table with your
        ,(3,'2021-01-01'      , false) -- actual table
         ) dat(id,"timestamp",alive)       -- ^^^^^^^^^^^^^^^^^^^^
         cross join lateral (select to_char(dat."timestamp",'YYYY-MM')) cnv("Period")
    group by cnv."Period"
)
select *
      ,("False" * 1.0 / "True")::numeric(10,2) as "false/true"
from agg
order by "Period"
;

The lateral join just converts the date to yyyy-mm format once, so the result can be referenced multiple times
The CTE counts the number of true & false once, so the results can be referenced in the ratio calculation. Note that by leaving out the else on the case statements, the sum() will return null for periods in which there are no occurrences of true. The ratio will then show null rather than produce a divide by zero error
The ratio calculation multiplies the integer value by a numeric value to avoid integer division rounding. It then casts the result to a numeric type with 2 decimal places.

CodePudding user response：

You can use a FILTER clause for each condition (alive and not alive) and then aggregate it by the formatted timestamp using to_char, e.g.

SELECT 
  to_char(timestamp,'MON-YYYY'),
  count(*) FILTER (WHERE alive) AS alive,
  count(*) FILTER (WHERE NOT alive) AS not_alive,
  count(*) FILTER (WHERE alive)::numeric / 
    nullif(count(*) FILTER (WHERE NOT alive),0)::numeric AS "true/false"
FROM t
GROUP BY to_char(timestamp,'MON-YYYY');

Demo: db<>fiddle