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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
