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?.
select version();
| version |
|---|
| PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
CREATE TABLE test (
order_id VARCHAR(1),item VARCHAR(100), category VARCHAR(100), qty INT
);
INSERT INTO test (order_id, item, category, qty) VALUES
('A', 'Eggs', 'Food -> Breakfast', 1),
('A', 'Avocado', 'Food -> Breakfast', 2),
('A', 'Coke', 'Drinks -> Cold drinks', 1),
('A', 'Flat White', 'Drinks -> Coffee', 1),
('B', 'Eggs', 'Food -> Breakfast', 1),
('B', 'Flat White', 'Drinks -> Coffee', 1),
('C', 'Eggs', 'Food -> Breakfast', 1),
('D', 'Latte', 'Drinks -> Coffee', 1);
CREATE TABLE
INSERT 0 8
SELECT order_id,
SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) AS food_qty,
SUM(CASE WHEN category LIKE 'Drinks%' THEN qty ELSE 0 END) AS drinks_qty
FROM test
GROUP BY order_id
HAVING SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) > 0
| order_id | food_qty | drinks_qty |
|---|---|---|
| B | 1 | 1 |
| C | 1 | 0 |
| A | 3 | 2 |
SELECT 3
WITH all_orders AS (
SELECT order_id,
SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) AS food_qty,
SUM(CASE WHEN category LIKE 'Drinks%' THEN qty ELSE 0 END) AS drink_qty
FROM test
GROUP BY order_id
HAVING SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) > 0
)
SELECT
CAST((SELECT COUNT(*) FROM all_orders WHERE drink_qty >= food_qty) * 100.0 / (SELECT COUNT(*) FROM all_orders) AS DECIMAL(5, 2)) AS pct_asmany_drinks_as_food,
CAST((SELECT COUNT(*) FROM all_orders WHERE drink_qty = 0) * 100.0 / (SELECT COUNT(*) FROM all_orders) AS DECIMAL(5, 2)) AS pct_no_drinks;
| pct_asmany_drinks_as_food | pct_no_drinks |
|---|---|
| 33.33 | 33.33 |
SELECT 1