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 illness (nature_of_illness VARCHAR(25), created_at TIMESTAMP);
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung', '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung', '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung', '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung', '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung', '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung', '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung', '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung', '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
SELECT
t3.c_year AS "Year",
t3.c_month AS "Month",
t3.il_mc AS "Tumour count",
t4.ill_nat AS "Type" FROM
(
SELECT c_year, c_month, il_mc FROM
(
SELECT
c_year,
c_month,
MAX(month_count) AS il_mc
FROM
(
SELECT nature_of_illness as illness,
EXTRACT(YEAR FROM created_at) AS c_year,
EXTRACT(MONTH FROM created_at) AS c_month,
COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
FROM illness
GROUP BY illness, c_year, c_month
ORDER BY c_year, c_month
) AS t1
GROUP BY c_year, c_month
) AS t2
) AS t3
JOIN
(
SELECT
EXTRACT(YEAR FROM created_at) AS t_year,
EXTRACT(MONTH FROM created_at) AS t_month,
nature_of_illness AS ill_nat,
COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
| Year | Month | Tumour count | Type |
|---|---|---|---|
| 2017 | 1 | 1 | Cervix |
| 2017 | 1 | 1 | Lung |
| 2017 | 2 | 3 | Lung |
| 2018 | 1 | 5 | Cervix |
| 2018 | 2 | 3 | Lung |