need help with a max query - SQL Server Forums

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

need help with a max query

Author

Topic

ghostfly
Starting Member

6 Posts

Posted - 2011-02-06 : 14:00:23

i get this error
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

SELECT max(salesCount)
FROM
(
SELECT COUNT(*)as salesCount
FROM Orders   <---line 8
Group by employeeid
)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-06 : 14:17:24

Need to alias the subquery

SELECT max(salesCount)
FROM
(
SELECT COUNT(*)as salesCount
FROM Orders
Group by employeeid
) x -- <--- Alias here --

ghostfly
Starting Member

6 Posts

Posted - 2011-02-06 : 14:40:44

ok i got that working thanx to you but how can i get the employee id to also show up that has the most sales?

my new code

SELECT  max(salesCount)as total_sales
FROM
(
SELECT employeeid, COUNT(*)as salesCount
FROM Orders
Group by employeeid 

) as totalSales

works great but the employee id doesnt show up

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 00:26:53

Check this one!

Select top 1 employeeId,COUNT(*) salesCount
from Orders
group by employeeId
order by salesCount desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources