Обсуждение: left join count
Hi All, I'm trying to retrieve the count of notes associated for each transactions for table energy_transactions. But I seem to be getting (after the join) the sum of amount x count of notes. Can someone enlighten me with this problem? Here's the query that I am working with. select energy_accounts_id, count(note) ,sum(case when t.fin_year = 2010 and t.fin_quarter = 1 then t.total_amount else 0 end) as amount_current ,sum(case when t.fin_year = 2009 and t.fin_quarter = 1 then t.total_amount else 0 end) as amount_last from energy_transactions t left join energy_notes n on (t.id = n.energy_transactions_id) group by energy_accounts_id, total_amount Thanks in advance :)
On 11/02/10 22:53, Greenhorn wrote: > But I seem to be getting (after the join) the sum of amount x count of > notes. Can someone enlighten me with this problem? > select > energy_accounts_id, count(note) > ,sum(case when t.fin_year = 2010 and t.fin_quarter = 1 > then t.total_amount else 0 end) as amount_current > ,sum(case when t.fin_year = 2009 and t.fin_quarter = 1 > then t.total_amount else 0 end) as amount_last > from energy_transactions t > left join energy_notes n on (t.id = n.energy_transactions_id) > group by energy_accounts_id, total_amount If you were to eliminate the group by and aggregates you would see one row for each match either side of the join. So - if t.id=123 had three notes then it would be repeated three times, with the details of each note. As a result, so is t.total_amount repeated three times. When you sum(t.total_amount) you will get three times the value you expected. How to solve this? Split the two parts of the query and join their results. Something like: SELECT t.energy_accounts_id, sum(coalesce(nc.note_count,0)) as note_count, sum(...) as amount_current, sum(...) as amount_last FROM energy_transactions t LEFT JOIN ( SELECT energy_transactions_id AS id, count(*) AS note_count FROM energy_notes GROUP BY energy_transactions_id ) AS nc ON t.id = nc.id GROUP BY ... The idea is that the subquery contains only one row for each id on the other side of the join. HTH -- Richard Huxton Archonet Ltd
On 12 February 2010 10:28, Richard Huxton <dev@archonet.com> wrote: > On 11/02/10 22:53, Greenhorn wrote: >> >> But I seem to be getting (after the join) the sum of amount x count of >> notes. Can someone enlighten me with this problem? > >> select >> energy_accounts_id, count(note) >> ,sum(case when t.fin_year = 2010 and t.fin_quarter = 1 >> then t.total_amount else 0 end) as amount_current >> ,sum(case when t.fin_year = 2009 and t.fin_quarter = 1 >> then t.total_amount else 0 end) as amount_last >> from energy_transactions t >> left join energy_notes n on (t.id = n.energy_transactions_id) >> group by energy_accounts_id, total_amount > > If you were to eliminate the group by and aggregates you would see one row > for each match either side of the join. So - if t.id=123 had three notes > then it would be repeated three times, with the details of each note. As a > result, so is t.total_amount repeated three times. When you > sum(t.total_amount) you will get three times the value you expected. > > How to solve this? Split the two parts of the query and join their results. > Something like: > > SELECT > t.energy_accounts_id, > sum(coalesce(nc.note_count,0)) as note_count, > sum(...) as amount_current, > sum(...) as amount_last > FROM > energy_transactions t > LEFT JOIN ( > SELECT energy_transactions_id AS id, count(*) AS note_count > FROM energy_notes > GROUP BY energy_transactions_id > ) AS nc > ON t.id = nc.id > GROUP BY ... > > > The idea is that the subquery contains only one row for each id on the other > side of the join. > > HTH > -- > Richard Huxton > Archonet Ltd > Thank you :) Solved!