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

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!