sql query required

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
sunilsangaraju
Member
Posts: 2
Joined: Mon Mar 10, 2008 5:14 pm

sql query required

Post by sunilsangaraju » Tue Mar 11, 2008 10:29 am

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

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

sql query-finding highest number of employees in department

Post by Krishna » Tue Mar 11, 2008 4:38 pm

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.

sunilsangaraju
Member
Posts: 2
Joined: Mon Mar 10, 2008 5:14 pm

Post by sunilsangaraju » Wed Mar 12, 2008 12:17 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....

Post Reply

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



Other References
Mainframe Tools and others