By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t (
id int, timestamp date, alive boolean
);
INSERT INTO t VALUES
(1,'2019-10-10',true),
(2,'2019-10-10',false),
(3,'2019-10-10',false),
(4,'2019-11-01',false);
4 rows affected
SELECT
to_char(timestamp,'MON-YYYY'),
count(*) FILTER (WHERE alive) AS "true",
count(*) FILTER (WHERE NOT alive) AS "false",
count(*) FILTER (WHERE NOT alive)::numeric * 1.0 /
nullif(count(*) FILTER (WHERE alive),0)::numeric AS "false/true"
FROM t
GROUP BY to_char(timestamp,'MON-YYYY');
| to_char | true | false | false/true |
|---|---|---|---|
| OCT-2019 | 1 | 2 | 2.0000000000000000 |
| NOV-2019 | 0 | 1 | null |