Schema:
CREATE TABLE "items" (
"id" SERIAL NOT NULL PRIMARY KEY,
"country" VARCHAR(2) NOT NULL,
"created" TIMESTAMP WITH TIME ZONE NOT NULL,
"price" NUMERIC(11, 2) NOT NULL
);
CREATE TABLE "payments" (
"id" SERIAL NOT NULL PRIMARY KEY,
"created" TIMESTAMP WITH TIME ZONE NOT NULL,
"amount" NUMERIC(11, 2) NOT NULL,
"item_id" INTEGER NULL
);
CREATE TABLE "extras" (
"id" SERIAL NOT NULL PRIMARY KEY,
"created" TIMESTAMP WITH TIME ZONE NOT NULL,
"amount" NUMERIC(11, 2) NOT NULL,
"item_id" INTEGER NULL
);
Data:
INSERT INTO items VALUES
(1, 'CZ', '2016-11-01', 100),
(2, 'CZ', '2016-11-02', 100),
(3, 'PL', '2016-11-03', 20),
(4, 'CZ', '2016-11-04', 150)
;
INSERT INTO payments VALUES
(1, '2016-11-01', 60, 1),
(2, '2016-11-01', 60, 1),
(3, '2016-11-02', 100, 2),
(4, '2016-11-03', 25, 3),
(5, '2016-11-04', 150, 4)
;
INSERT INTO extras VALUES
(1, '2016-11-01', 5, 1),
(2, '2016-11-02', 1, 2),
(3, '2016-11-03', 2, 3),
(4, '2016-11-03', 3, 3),
(5, '2016-11-04', 5, 4)
;
So, we have:
- 3 items in CZ in 1 in PL
- 370 earned in CZ and 25 in PL
- 350 cost in CZ and 20 in PL
- 11 extra earned in CZ and 5 extra earned in PL
Now I want to get answers for the following questions:
- How many items we had last month in every country?
- What was the total earned amount (sum of payments.amounts) in every country?
- What was the total cost (sum of items.price) in every country?
- What was the total extra earnings (sum of extras.amount) in every country?
With the following query (SQLFiddle):
SELECT
country AS "group_by",
COUNT(DISTINCT items.id) AS "item_count",
SUM(items.price) AS "cost",
SUM(payments.amount) AS "earned",
SUM(extras.amount) AS "extra_earned"
FROM items
LEFT OUTER JOIN payments ON (items.id = payments.item_id)
LEFT OUTER JOIN extras ON (items.id = extras.item_id)
GROUP BY 1;
The results are wrong:
group_by | item_count | cost | earned | extra_earned
----------+------------+--------+--------+--------------
CZ | 3 | 450.00 | 370.00 | 16.00
PL | 1 | 40.00 | 50.00 | 5.00
Cost and extra_earned for CZ are invalid - 450 instead of 350 and 16 instead of 11. Cost and earned for PL are also invalid - they are doubled.
I understand, that in case of LEFT OUTER JOIN
there will be 2 rows for item with items.id = 1 (and so on for other matches), but I don't know how to build a proper query.
Questions:
- How to avoid wrong results in aggregation in queries on multiple tables?
- What is the best way to calculate sum over distinct values (items.id in that case)?
PostgreSQL version: 9.6.1
OUTER APPLY
and usingLATERAL
joins instead.Seq Scan
on payments, which means that the statistic will be recalculated on all items. I didn't mention this in the question but I want to filter items by creation time also, so I'll need only specific subset of the aggregated data. I'll update the question