Skip to content
Advertisement

Getting sum of two rows in an inner joined table

I have these two tables;

  1. trips
id date revenue
1 01/01/2020 5000
2 01/01/2020 3000
3 02/01/2020 4000
4 02/01/2020 2000
  1. 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
Advertisement