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?.
WITH yourTable AS (
SELECT 'john' AS first, 'doe' AS last, '2020-03-18'::date AS date, NULL AS pos UNION ALL
SELECT 'harris', 'potter', NULL, '2021-06-10'::date UNION ALL
SELECT 'john', 'doe', '2021-05-10'::date, NULL UNION ALL
SELECT 'harris', 'potter', '2021-06-14'::date, NULL UNION ALL
SELECT 'jessica', 'potter', '2021-06-14'::date, NULL UNION ALL
SELECT 'kermit', 'foster', NULL, NULL
)
SELECT
first,
last,
COUNT(date) AS totalDoses,
2 - (COUNT(*) FILTER (WHERE pos IS NOT NULL) > 0)::int AS totalRequieredDoses
FROM yourTable
GROUP BY
first,
last
ORDER BY
COUNT(date) DESC,
first,
last;
| first | last | totaldoses | totalrequiereddoses |
|---|---|---|---|
| john | doe | 2 | 2 |
| harris | potter | 1 | 1 |
| jessica | potter | 1 | 2 |
| kermit | foster | 0 | 2 |
SELECT 4