Aggregate function problem - count()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Aggregate function problem - count()

Author

Topic

xtech
Starting Member

6 Posts

Posted - 2009-07-13 : 16:37:06

Hi,

I've written a SQL script to retrieve data on service calls clients have had within a specific time frame, with associated technician name, etc. and it works perfectly. However, when I attempt to filter it further using aggregate functions, and have only clients with more than 3 service calls, I run into a problem - nothing is returned. The failing SQL with the count() functions is listed below. Would appreciate any insight into the issue.
Thanks to one and all.

xtech
(Apologies for the double posting - forgot the code)

select
dbo.SERVICEHISTORY_MAS.account,
count(dbo.SERVICEHISTORY_MAS.account)AS Num_Of_Service_Calls,
dbo.SERVICEHISTORY_MAS.svc_dt,
dbo.SERVICEHISTORY_MAS.callid,
dbo.SERVICEHISTORY_MAS.troublecd,
dbo.troublecode.descr,
dbo.SERVICEHISTORY_MAS.techid,
dbo.technician.name,
dbo.CUSTOMER.NAME,
dbo.CUSTOMER.STREET,
dbo.CUSTOMER.CITY,
dbo.CUSTOMER.STATE,
dbo.CUSTOMER.ZIP

from dbo.SERVICEHISTORY_MAS

INNER JOIN dbo.CUSTOMER
ON dbo.CUSTOMER.ACCOUNT=dbo.SERVICEHISTORY_MAS.ACCOUNT

INNER JOIN dbo.troublecode
ON dbo.troublecode.troublecd=dbo.servicehistory_mas.troublecd

INNER JOIN dbo.technician
ON dbo.technician.techid=dbo.servicehistory_mas.techid

where svc_dt between '2008-04-01' AND '2009-06-18'
and SERVICEHISTORY_MAS.troublecd IN ('002','001','024')

group by dbo.SERVICEHISTORY_MAS.account,
dbo.SERVICEHISTORY_MAS.svc_dt,
dbo.SERVICEHISTORY_MAS.callid,
dbo.SERVICEHISTORY_MAS.troublecd,
dbo.troublecode.descr,
dbo.SERVICEHISTORY_MAS.techid,
dbo.technician.name,
dbo.CUSTOMER.NAME,
dbo.CUSTOMER.STREET,
dbo.CUSTOMER.CITY,
dbo.CUSTOMER.STATE,
dbo.CUSTOMER.ZIP
having (count(dbo.SERVICEHISTORY_MAS.account)>=3)
Order By Num_Of_Service_Calls

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 16:44:31

If you take out the having clause and include an:
ORDER BY count(dbo.SERVICEHISTORY_MAS.account) DESC

What is the largest value of [Num_Of_Service_Calls] (top of the list)

Be One with the Optimizer
TG

xtech
Starting Member

6 Posts

Posted - 2009-07-13 : 16:55:11

Well, when I run this snippet by itself, the highest number of service calls for a client is 226. The problem arises when I try to incorporate this function, as I did in my previous example, to retrieve all clients with 3 or more service calls only.

Thanks.

xtech

xtech
Starting Member

6 Posts

Posted - 2009-07-13 : 16:56:47

quote:
Originally posted by TG

If you take out the having clause and include an:
ORDER BY count(dbo.SERVICEHISTORY_MAS.account) DESC

What is the largest value of [Num_Of_Service_Calls] (top of the list)

Be One with the Optimizer
TG

snippet used.

select account,
count(account)AS Num_Of_Service_Calls
from dbo.SERVICEHISTORY_MAS
group by account
having (count(account)>=3)
Order By Num_Of_Service_Calls

xtech
Starting Member

6 Posts

Posted - 2009-07-13 : 17:06:20

Tried your suggestion but only get clients with 1 service call. I'm trying to get all clients with more than 3 service calls.
Thanks for the try anyway.

xtech

quote:
Originally posted by TG

If you take out the having clause and include an:
ORDER BY count(dbo.SERVICEHISTORY_MAS.account) DESC

What is the largest value of [Num_Of_Service_Calls] (top of the list)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 17:16:19

quote:
Originally posted by xtech

Well, when I run this snippet by itself, the highest number of service calls for a client is 226. The problem arises when I try to incorporate this function, as I did in my previous example, to retrieve all clients with 3 or more service calls only.

Thanks.

xtech

What I'm trying to say is to use your exact statement with all the other tables included as well as your GROUP BY but just remove the HAVING clause. I suspect that the GROUP BY clause is causing your COUNT to never have more than 3 values. In fact I was expecting that all the COUNTs would be 1.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 17:20:35

Perhaps if you start with a derived table which only has the accounts with at least 3 that would work:


SELECT sh.account
       ,d.Num_Of_Service_Calls
       ,...
from   (
       select account
              ,count(account)AS Num_Of_Service_Calls
       from   dbo.SERVICEHISTORY_MAS
       group by account
       having count(account)>=3
       ) d
INNER JOIN   dbo.SERVICEHISTORY_MAS sh
       on sh.account = d.account
INNER JOIN ....

Be One with the Optimizer
TG

xtech
Starting Member

6 Posts

Posted - 2009-07-14 : 11:08:43

TG --

I've implemented your suggestions and it seems to be working. I'm in the process of verifying the results to be sure they're accurate.

I'm posting the changes below for the benefit of anyone else and also - if you should see anything that stands out as a problem, I'd appreciate if you would point it out.

Otherwise, thanks very much for your help.
--xtech

SELECT

d.account,
d.Num_Of_Service_Calls,
sh.svc_dt,
sh.callid,
sh.troublecd,
dbo.troublecode.descr,
sh.techid,
dbo.technician.name,
dbo.CUSTOMER.NAME,
dbo.CUSTOMER.STREET,
dbo.CUSTOMER.CITY,
dbo.CUSTOMER.STATE,
dbo.CUSTOMER.ZIP

FROM

(
select account,
count(account)AS Num_Of_Service_Calls
from dbo.SERVICEHISTORY_MAS
group by account
having (count(account)>=3)
) d

INNER JOIN dbo.SERVICEHISTORY_MAS sh
on sh.account = d.account

INNER JOIN dbo.CUSTOMER
ON dbo.CUSTOMER.ACCOUNT=sh.ACCOUNT

INNER JOIN dbo.troublecode
ON dbo.troublecode.troublecd=sh.troublecd

INNER JOIN dbo.technician
ON dbo.technician.techid=sh.techid

WHERE svc_dt between '2008-04-01' AND '2009-06-18'
and sh.troublecd IN ('002','001','024')

ORDER BY Num_Of_Service_Calls

quote:

Originally posted by TG

Perhaps if you start with a derived table which only has the accounts with at least 3 that would work:


SELECT sh.account
       ,d.Num_Of_Service_Calls
       ,...
from   (
       select account
              ,count(account)AS Num_Of_Service_Calls
       from   dbo.SERVICEHISTORY_MAS
       group by account
       having count(account)>=3
       ) d
INNER JOIN   dbo.SERVICEHISTORY_MAS sh
       on sh.account = d.account
INNER JOIN ....

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources