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 sales (
id SERIAL PRIMARY KEY,
event_date DATE,
customer VARCHAR,
orderID VARCHAR,
sales_volume DECIMAL
);
INSERT INTO sales
(event_date, customer, orderID, sales_volume)
VALUES
('2020-01-08', 'Customer_A', 'Order_001', '130'),
('2020-01-12', 'Customer_A', 'Order_002', '120'),
('2020-01-22', 'Customer_A', 'Order_003', '115'),
('2020-01-22', 'Customer_C', 'Order_001', '300'),
('2020-01-23', 'Customer_C', 'Order_002', '500'),
('2020-04-08', 'Customer_B', 'Order_001', '325'),
('2020-04-12', 'Customer_B', 'Order_002', '875'),
('2020-04-15', 'Customer_B', 'Order_003', '910'),
('2020-04-20', 'Customer_B', 'Order_004', '723'),
('2020-04-30', 'Customer_C', 'Order_003', '665'),
('2020-06-01', 'Customer_B', 'Order_005', '982'),
('2020-06-15', 'Customer_B', 'Order_006', '100'),
('2020-06-19', 'Customer_C', 'Order_004', '250'),
('2020-06-20', 'Customer_C', 'Order_005', '322'),
('2020-06-25', 'Customer_A', 'Order_004', '445');
15 rows affected
SELECT
s.customer,
s.orderID,
s.event_date,
SUM(s.sales_volume) AS sales_volume
FROM sales s
GROUP BY 1,2,3
ORDER BY 1,2,3;
| customer | orderid | event_date | sales_volume |
|---|---|---|---|
| Customer_A | Order_001 | 2020-01-08 | 130 |
| Customer_A | Order_002 | 2020-01-12 | 120 |
| Customer_A | Order_003 | 2020-01-22 | 115 |
| Customer_A | Order_004 | 2020-06-25 | 445 |
| Customer_B | Order_001 | 2020-04-08 | 325 |
| Customer_B | Order_002 | 2020-04-12 | 875 |
| Customer_B | Order_003 | 2020-04-15 | 910 |
| Customer_B | Order_004 | 2020-04-20 | 723 |
| Customer_B | Order_005 | 2020-06-01 | 982 |
| Customer_B | Order_006 | 2020-06-15 | 100 |
| Customer_C | Order_001 | 2020-01-22 | 300 |
| Customer_C | Order_002 | 2020-01-23 | 500 |
| Customer_C | Order_003 | 2020-04-30 | 665 |
| Customer_C | Order_004 | 2020-06-19 | 250 |
| Customer_C | Order_005 | 2020-06-20 | 322 |