Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count problem

Status
Not open for further replies.

morfasie

IS-IT--Management
Mar 28, 2004
85
ZA
Hi,

I have this query that works fine,
select a.`group name`,a.umbrella_premium,a.money_received,sum(d.amount_paid)
from schedule_premiums a,groups c
left outer join claim_payment_new d
on c.`group name` = d.`group name`
where a.`group name` = c.`group name`
and c.`group name` = b.`group name`
and c.`date deleted` is null
and a.month = '2005-09-01'
and d.date_paid = '2005-09-01'
group by a.`group name`

but then I want to add a count from another table, and when I do that it increases the sum(d.amount_paid) almost four times. Why is that??

select a.`group name`,a.umbrella_premium,a.money_received,sum(d.amount_paid),count(*)
from schedule_premiums a,groups c,productioncalc b
left outer join claim_payment_new d
on c.`group name` = d.`group name`
where a.`group name` = c.`group name`
and c.`group name` = b.`group name`
and c.`date deleted` is null
and a.month = '2005-09-01'
and d.date_paid = '2005-09-01'
group by a.`group name`

 
it's called a cross join effect

for every row in the original query, each one of them is joined to several new b rows that match

by the way, you should never mix old-school table list syntax with JOIN syntax

replace this --

from schedule_premiums a,groups c,productioncalc b
left outer join claim_payment_new d
on c.`group name` = d.`group name`
where a.`group name` = c.`group name`
and c.`group name` = b.`group name`
and c.`date deleted` is null
and a.month = '2005-09-01'
and d.date_paid = '2005-09-01'

with this --

from schedule_premiums a
inner
join groups c
on a.`group name` = c.`group name`
and c.`date deleted` is null
inner
join productioncalc b
on c.`group name` = b.`group name`
left outer
join claim_payment_new d
on c.`group name` = d.`group name`
and d.date_paid = '2005-09-01'
where a.month = '2005-09-01'

r937.com | rudy.ca
 
Hi thanks, I have used your code but had to put in a group by, so I putin the group by a.`group name`, but still the values are wrong, in the 100 thousands where they should maybe be 10000.


thanks
 
i didn't say my code would fix your count problem, i tried to say that your count problem was because of the one-to-many effects that joining to the 4th table caused

r937.com | rudy.ca
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top