Convert MS Access Query with Count Function

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

Convert MS Access Query with Count Function

Author

Topic

hk1
Starting Member

3 Posts

Posted - 2009-12-11 : 17:41:40

Now that I switched from an Access database to MS SQL Server, this query is failing and I'm not sure why. Does anyone know why?

SELECT * from (SELECT Distinct tblIPLocations.strCountry, Count(tblIpLocations.strCountry) as DomainCount from tblIPLocations INNER JOIN tblVisits on tblIpLocations.strIP = tblVisits.strIPAddress WHERE tblVisits.dteDate = '12/11/2009' Group By strCountry) Order By DomainCount DESC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-11 : 17:44:19

Could you post the error?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-11 : 18:56:45

[code]SELECT *
FROM (SELECT DISTINCT tbliplocations.strcountry,
Count(tbliplocations.strcountry) AS domaincount
FROM tbliplocations
INNER JOIN tblvisits
ON tbliplocations.strip = tblvisits.stripaddress
WHERE tblvisits.dtedate = '20091211'
GROUP BY strcountry) as alias_for_derived_table
ORDER BY domaincount DESC [/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-12 : 01:45:11

It is because, unlike Other DBMSs, SQL Server requires alias name for a derived table

Madhivanan

Failing to plan is Planning to fail

hk1
Starting Member

3 Posts

Posted - 2009-12-14 : 11:47:43

Thanks a lot guys. That fixed the problem.

Now I get to learn how to write stored procedures.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources