Обсуждение: left join count

Hi All,
I'm trying to retrieve the count of notes associated for each
transactions for table energy_transactions.
But I seem to be getting (after the join) the sum of amount x count of
notes.  Can someone enlighten me with this problem?
Here's the query that I am working with.

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

Thanks in advance :)

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

On 12 February 2010 10:28, Richard Huxton <dev@archonet.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!