View previous topic :: View next topic
|
Author |
Message |
SSR Warnings : 1 New User
Joined: 26 Feb 2006 Posts: 38
|
|
|
|
Hi,
My requirement is
I have a table with the below columns
EMP_NO, EMP_NAME,DEPT_NO,DEPT_NAME
I want Department with maximum number of employees and the count.
Regards
SSR |
|
Back to top |
|
|
mbr_raja
New User
Joined: 15 Nov 2004 Posts: 26 Location: Chennai, India
|
|
|
|
Check the query and you will get the desired result as you expected.
select max(DEPT_NO), count(*) from < Table Name > ; |
|
Back to top |
|
|
sihanature Warnings : 1 New User
Joined: 01 Sep 2005 Posts: 33
|
|
|
|
That query looks fine.
Execute and see for desired results.
Regards |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
SSR...
Try this query...
Code: |
SELECT COUNT(EMP_NO) AS EMPCOUNT, DEPT_NO
FROM DB2ID.TABLENAME
GROUP BY DEPT_NO
ORDER BY EMPCOUNT DESC FETCH FIRST ROW ONLY; |
|
|
Back to top |
|
|
SSR Warnings : 1 New User
Joined: 26 Feb 2006 Posts: 38
|
|
|
|
Thanks a lot priyesh, its working fine. |
|
Back to top |
|
|
SSR Warnings : 1 New User
Joined: 26 Feb 2006 Posts: 38
|
|
|
|
hi priyesh,
Is there any way by which we could used the max function for this as this would be a simple one, but i did various trials but no use.
Regards
Sundar |
|
Back to top |
|
|
Rameshs
New User
Joined: 15 Jun 2005 Posts: 53 Location: India, Chennai
|
|
|
|
Code: |
SELECT EMPCOUNT,DEPT_NO
FROM TABLE
(SELECT COUNT(EMP_NO) AS EMPCOUNT, DEPT_NO
FROM DB2ID.TABLENAME
GROUP BY DEPT_NO
ORDER BY EMPCOUNT DESC FETCH FIRST ROW ONLY) AS TB
WHERE EMPCOUNT =MAX(EMPCOUNT);
|
|
|
Back to top |
|
|
|