Query Problem when using aggregate functions

Posted by Bhanubysani under Sql Server on 5/19/2011 | Points: 10 | Views : 1288 | Status : [Member] | Replies : 2
Hi

I have a requirement to display the count of sms,count of emails,count of click to call info from smsleads,email leads,clicktocallinfo tables based on ypid of company..that ypid belongs to ypinformation table...

i wrote the query like this

select Count(YPSmsLeads.SmsID) as smsleads ,Count(YPEmailLeads.EmailID) as emailleads,count(YPCallNowInfo.ID) as clicktocallinfo ,ypinformation.ypid,ypinformation.CompanyName from ypinformation inner join YPSmsLeads on YPSmsLeads.YPID=ypinformation.YPID inner join YPEmailLeads on YPEmailLeads.YPID=ypinformation.YPID inner join YPCallNowInfo on ypinformation.YPID=YPCallNowInfo.YPID where ypinformation.Category='testcat1' group by ypinformation.CompanyName,ypinformation.YPID

The problem is if individually runt the queries for count of smsleads as like this select COUNT(emailid) from YPEmailLeads where YPID=47482 it will showing as 4...it is correct...

but executing the above query it will be dispalying as 640 for all smsleads,emailleads,click tocall info....

Plz help me in writing the query perfectly

Regards,
Bhanu Prakash Bysani



Responses

Posted by: Robin_ahuja2008 on: 6/15/2011 [Member] Starter | Points: 25

Up
0
Down
Just check your where clause or group by statement


Regards,
Robin Ahuja

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 6/15/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

As "Robin Ahuja" said, the reason may be....

1. The tables you have JOINed may produeces duplicate/repeated rows.. So, the COUNT, SUM also getting increased. so, you have to check the is there any chance to put additional columns on ON clause of the JOIN or additional conditions on WHERE clause to avoid data repeatision.

2. So, first try to identify the repeated data through... normal SELECT statement.. without using any aggregate (COUNT, SUM) and try to filter the rows/avoide the duplicate data.

3. finally, put the aggregate on that SELECT statement...

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response