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 occu_cap(
id int,
occupancy INT,
capacity INT
);
INSERT INTO occu_cap VALUES (1,1232,1630);
INSERT INTO occu_cap VALUES (2,0,658);
INSERT INTO occu_cap VALUES (3,0,0);
1 rows affected
1 rows affected
1 rows affected
SELECT *,AVG(CASE WHEN capacity <> 0 AND occupancy <> 0 THEN ((occupancy::decimal/capacity * 1.0) * 100) END) OVER(PARTITION BY id)
FROM occu_cap
| id | occupancy | capacity | avg |
|---|---|---|---|
| 1 | 1232 | 1630 | 75.582822085889570552000 |
| 2 | 0 | 658 | null |
| 3 | 0 | 0 | null |