Any roundabouts to max(count) - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Any roundabouts to max(count)

Author

Topic

venkey_no1
Starting Member

2 Posts

Posted - 2005-03-12 : 08:52:32

Hey guys, i have a small problem. Most of you would have the master database with u. I want to get the output as deptno having the most number of employees.

Kindly help
Thankyou

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-12 : 09:45:41

select DeptNum, count(EmployeeId) as EmpNum
from MyTable
group by DeptNum
order by 2

or

select DeptNum, max(EmployeeId) as MaxEmpNum
from MyTable
group by DeptNum

See count and max aggregates in BOL = Books OnLine = SQL server help.

Go with the flow & have fun! Else fight the flow

venkey_no1
Starting Member

2 Posts

Posted - 2005-03-12 : 14:07:46

spirit1 you are nowhere close to the answer.
select DeptNum, count(EmployeeId) as EmpNum
from MyTable
group by DeptNum
order by 2
This would retrun me 2 columns. I want only one value to be the output---> Deptno
your other solution
the same problem with this.
select DeptNum, max(EmployeeId) as MaxEmpNum
from MyTable
group by DeptNum

the problem is i cannot query 'max(count(*))'
The output should be something like this
Deptno
-------
1001

not something like this:
Deptno Empnum
---------------
1001 10

so... its not that simple.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-12 : 14:19:21

[code]select top 1
DeptNum
from
MyTable
group by
DeptNum
order by
count(*) desc[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources