Subquery 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

General SQL Server Forums

New to SQL Server Programming

Subquery Count

Author

Topic

freshman11
Starting Member

13 Posts

Posted - 2011-06-25 : 23:31:53

hi need your help to solve problem below.
In which year did most people join the company? Display the year
and the number of employees.
emp table columns: empno,ename,job,mgr,hiredate,sal,comm,deptno

below should be retrieved:
year no_of_emp
----------- -----------
1984 8

(1 row(s) affected)

i tried using the below subquery but still generated 2 rows.
select year(x.hiredate) year, count(x.empno) no_of_emp
from emp x
where exists (
select y.empno
from emp y
where x.empno > y.empno)
group by year(x.hiredate)

please help. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-26 : 05:00:59

[code]
select top 1 year(x.hiredate), count(x.empno) no_of_emp
from emp
group by year(x.hiredate)
order by count(x.empno) desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

freshman11
Starting Member

13 Posts

Posted - 2011-06-26 : 06:54:14

thanks so much! you are really great! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources