IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL Query with maximum number of employees and the count


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Thu Sep 07, 2006 2:24 pm
Reply with quote

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
View user's profile Send private message
mbr_raja

New User


Joined: 15 Nov 2004
Posts: 26
Location: Chennai, India

PostPosted: Thu Sep 07, 2006 5:32 pm
Reply with quote

Check the query and you will get the desired result as you expected.

select max(DEPT_NO), count(*) from < Table Name > ;
Back to top
View user's profile Send private message
sihanature
Warnings : 1

New User


Joined: 01 Sep 2005
Posts: 33

PostPosted: Thu Sep 07, 2006 5:50 pm
Reply with quote

That query looks fine.
Execute and see for desired results.

Regards
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Thu Sep 07, 2006 6:54 pm
Reply with quote

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
View user's profile Send private message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Fri Sep 08, 2006 9:46 am
Reply with quote

Thanks a lot priyesh, its working fine.
Back to top
View user's profile Send private message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Fri Sep 08, 2006 12:20 pm
Reply with quote

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
View user's profile Send private message
Rameshs

New User


Joined: 15 Jun 2005
Posts: 53
Location: India, Chennai

PostPosted: Fri Sep 08, 2006 3:02 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Generate random number from range of ... COBOL Programming 3
Search our Forums:

Back to Top