GROUP BY ... HAVING not working

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

SQL Server 2008 Forums

Transact-SQL (2008)

GROUP BY ... HAVING not working

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-24 : 17:51:02

Why am I getting columns with a count of 0 in this?

SELECT v_CommonApp.AppName, COUNT(v_SMSAppdata.appID) AS CountOfApps
FROM v_CommonApp
LEFT JOIN v_SMSAppData ON v_SMSAppData.appID = v_CommonApp.AppID
GROUP BY v_CommonApp.AppName
HAVING COUNT(v_CommonApp.AppID) > 0
ORDER BY v_CommonApp.AppName

Thank you.

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-24 : 17:56:20

Cuz v_CommonApp.AppID is on the LEFT side of the join and appID is on the RIGHT.

I think chaning changing:
HAVING COUNT(v_CommonApp.AppID) > 0

To:
HAVING COUNT(v_SMSAppdata.appID) > 0

will solve the issue.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-24 : 18:07:11

It worked. That was silly on my part. Thanks for the other set of eyes.

Duane

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-24 : 18:20:19

FWIW, that turns your LEFT JOIN into an INNER JOIN.

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-25 : 17:20:13

quote:
Originally posted by Bustaz Kool

FWIW, that turns your LEFT JOIN into an INNER JOIN.

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

How is that? I understand the basics of JOINs, but some of these details I don't. If you can explain this, maybe it will help me to understand more. Thanks.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources