On 12 February 2010 10:28, Richard Huxton <dev@xxxxxxxxxxxx> 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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
