I am breaking my head on joining of three tables. I have recreated a simple test case where I see the same problem, so it looks I make a fundamental mistake in my join query:
I have three tables:
id (PK)| date_closed
155 | '2018-04-17 10:08'
156 | '2018-03-17 10:08'
pizza | '2018-02-17 10:08'
id (FK) | source | quantity
155 | market | 300
155 | sawdust| 200
id | case_id (FK) | kg_bagged
X | 155 | 123
Y | 155 | 90
These tables I want to join to compare the total amounts per 'case' in quantity column and kg_bagged. So the case table has a 1:* many relationship to the other two. Therefore I make a join query like this:
SUM(kg_bagged)/SUM(quantity) AS reduction_factor
JOIN bagged ON case.id = bagged.case_id
JOIN registration ON case.id = registration.id
Than I would think this would be a correct query, but Postgres tells me I have to add case.id, date_closed to the group by clause. So I add this:
GROUP BY case.id, date_closed;
This code is running without errors, but it shows 1000 for the quanity at case 155 not the expected 500 (200+300). This behaviour only appears when there is more than 1 record. When joining only 1 table to the case table it also works fine. Can someone see the mistake made at the JOIN query?
I also tried using a subquery for joining two tables and than use a join on the table left, but it gave me similar results