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 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit |
create table mytable (asofdate date, columna int, columnb int, columnc int);
insert into mytable (asofdate, columna, columnb, columnc)
values
(date '2021-05-25', 12, 2, 3),
(date '2021-05-25', 12, 2, 3),
(date '2021-05-25', 12, 2, 3),
(date '2021-05-25', 12, 1, 3),
(date '2021-05-25', 12, 1, 3),
(date '2021-05-25', 12, 1, 3);
6 rows affected
SELECT
AsOfDate,
round(ColumnA/sum(ColumnB) * (ColumnC),4) As Monthly_Return
from mytable
WHERE
AsOfDate BETWEEN '2021-05-25' AND '2021-05-26'
GROUP BY AsOfDate, ColumnA,ColumnB,ColumnC;
| asofdate | monthly_return |
|---|---|
| 2021-05-25 | 12.0000 |
| 2021-05-25 | 6.0000 |
SELECT
asofdate,
SUM(monthly_return)
FROM
(
SELECT
AsOfDate,
round(ColumnA/sum(ColumnB) * (ColumnC),4) As Monthly_Return
from mytable
WHERE
AsOfDate BETWEEN '2021-05-25' AND '2021-05-26'
GROUP BY AsOfDate, ColumnA,ColumnB,ColumnC
) subquery
GROUP BY asofdate
ORDER BY asofdate;
| asofdate | sum |
|---|---|
| 2021-05-25 | 18.0000 |