I have these two tables;
- trips
id | date | revenue |
---|---|---|
1 | 01/01/2020 | 5000 |
2 | 01/01/2020 | 3000 |
3 | 02/01/2020 | 4000 |
4 | 02/01/2020 | 2000 |
- expenses
id | tripid | amount |
---|---|---|
1 | 1 | 500 |
2 | 1 | 300 |
3 | 2 | 400 |
4 | 2 | 200 |
5 | 2 | 700 |
I would like to get the sum of revenue collected in a day AND sum of expenses in a day. I have the following sql which gives me results but the sums are entirely wrong.
SELECT i.id, sum(i.revenue) as total, i.date trip , sum(c.amount) as exp, c.tripid expenses FROM trip i INNER JOIN expenses c ON i.id = c.tripid GROUP BY i.date ORDER BY trip DESC
Advertisement
Answer
You can preaggregate the expenses by trip, and then aggregate again in the outer query:
select t.date, sum(t.revenue) as revenue, coalesce(sum(e.expense), 0) as expense from trips t left join ( select tripid, sum(amount) as expense from expenses group by tripid ) e on e.tripid = t.id group by t.date