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 polls (id INTEGER, title TEXT, owner_username TEXT);
CREATE TABLE options (id INTEGER PRIMARY KEY, option_text TEXT, poll_id INTEGER);
CREATE TABLE votes (username TEXT, option_id INTEGER, FOREIGN KEY(option_id) REFERENCES options(id));
INSERT INTO polls VALUES (1, 'Flask vs. Django', 'jose');
INSERT INTO polls VALUES (2, 'Python vs. Java', 'rolf');
INSERT INTO polls VALUES (3, 'Windows vs. Mac', 'bob');
INSERT INTO options VALUES (1, 'Flask', 1);
INSERT INTO options VALUES (2, 'Django', 1);
INSERT INTO options VALUES (3, 'It Depends', 1);
INSERT INTO options VALUES (4, 'Python', 2);
INSERT INTO options VALUES (5, 'Java', 2);
INSERT INTO options VALUES (6, 'Windows', 3);
INSERT INTO options VALUES (7, 'Mac', 3);
INSERT INTO votes VALUES ('jose', 1);
INSERT INTO votes VALUES ('charlie', 1);
INSERT INTO votes VALUES ('ammar', 1);
INSERT INTO votes VALUES ('rolf', 2);
INSERT INTO votes VALUES ('bob', 2);
INSERT INTO votes VALUES ('anne', 4);
INSERT INTO votes VALUES ('eric', 4);
INSERT INTO votes VALUES ('jose', 4);
INSERT INTO votes VALUES ('charlie', 4);
INSERT INTO votes VALUES ('ammar', 4);
INSERT INTO votes VALUES ('rolf', 4);
INSERT INTO votes VALUES ('bob', 4);
INSERT INTO votes VALUES ('anne', 5);
INSERT INTO votes VALUES ('eric', 5);
INSERT INTO votes VALUES ('bob', 4);
INSERT INTO votes VALUES ('anne', 6);
INSERT INTO votes VALUES ('eric', 7);
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
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
options.id,
options.option_text,
COUNT(votes.option_id) as vote_count,
COUNT(votes.option_id) / SUM(COUNT(votes.option_id)) OVER() * 100.0 as vote_percentage
FROM options
LEFT JOIN votes on options.id = votes.option_id
WHERE options.poll_id = 1
GROUP BY options.id;
| id | option_text | vote_count | vote_percentage |
|---|---|---|---|
| 1 | Flask | 3 | 60.000000000000000000000 |
| 2 | Django | 2 | 40.000000000000000000000 |
| 3 | It Depends | 0 | 0.000000000000000000000 |