Re: left join count

On 12 February 2010 10:28, Richard Huxton <dev(at)archonet(dot)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!