Please help me out in writing a query to the below req.
I have a employee table with say 11 employees. I want to extract the dept number with max no of employees.
The table goes below.
EMP_NO DEPT_NO
01 D01
02 D02
03 D03
04 D01
05 D03
06 D01
07 D02
08 D01
09 D02
10 D03
11 D01
The query should return the DEPT_NO with max no of employees. Ie., D01 5(no of employees in this dept)
Thanks in Advance :lol: ,
Sunil
sql query required
Moderators: Kalicharan, Moderator Group
sql query-finding highest number of employees in department
One way..
SELECT DEPT_NO, COUNT(*) AS AA
FROM DEPARTMENT
GROUP BY DEPT_NO
ORDER BY AA DESC
FETCH FIRST ROW ONLY
You can do the same with correlated subquery.
But it consumes more resources than this query.
SELECT DEPT_NO, COUNT(*) AS AA
FROM DEPARTMENT
GROUP BY DEPT_NO
ORDER BY AA DESC
FETCH FIRST ROW ONLY
You can do the same with correlated subquery.
But it consumes more resources than this query.
Regards,
Krishna
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
Krishna
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
-
- Member
- Posts: 2
- Joined: Mon Mar 10, 2008 5:14 pm
Hi Krishna,
Thank you very much for query posted.
I have tried this query and it working fine, but i am facing problem when there are two departments having equal number of employees.
example:- D01 5 Employees D02 5 Employees D03 4 Employees
If i have a scenario like the above example how do i get both the departments D01 & D02
Also please let me know how to handle ,COUNT(*) AS AA in the query u hav posted, programatically.
I have used it in the program in the below way but it is giving me an error stating movement of rows can be done only to host variables.
EXEC SQL
SELECT DEPT_NO, COUNT(*) AS AA
INTO :WS-DEPTNO (HOST-VARIABLE),
:WS-COUNT (WORKING STORAGE VARIABLE)
FROM DEPARTMENT
GROUP BY DEPT_NO
ORDER BY AA DESC
FETCH FIRST ROW ONLY
END-EXEC
Thanks in Advance,
Sunil....
Thank you very much for query posted.
I have tried this query and it working fine, but i am facing problem when there are two departments having equal number of employees.
example:- D01 5 Employees D02 5 Employees D03 4 Employees
If i have a scenario like the above example how do i get both the departments D01 & D02
Also please let me know how to handle ,COUNT(*) AS AA in the query u hav posted, programatically.
I have used it in the program in the below way but it is giving me an error stating movement of rows can be done only to host variables.
EXEC SQL
SELECT DEPT_NO, COUNT(*) AS AA
INTO :WS-DEPTNO (HOST-VARIABLE),
:WS-COUNT (WORKING STORAGE VARIABLE)
FROM DEPARTMENT
GROUP BY DEPT_NO
ORDER BY AA DESC
FETCH FIRST ROW ONLY
END-EXEC
Thanks in Advance,
Sunil....
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page