Re: left join count

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