Query Problem when using aggregate functions

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts