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