# Thread: Count of Max(Salary) for each Department

1. Registered User
Join Date
Oct 2011
Posts
2

## Unanswered: Count of Max(Salary) for each Department

Consider following table:

EmpID EmpName Salary Dept
1 Pankaj 1000 Test
2 Abhey 1000 Test
3 Prachee 800 Test
4 Sumeet 1100 Quality
5 Akshat 1200 Quality
6 Gaurav 1500 Management
7 Ranjan 1500 Management

I need to find out the Count of all those employees who are receiving Max(Salary) in each department. So the output should be:
Dept MaX(Salary) Count
Test 1000 2
Quality 1100 1
Management 1500 2

2. Registered User
Join Date
Oct 2011
Posts
2

## Found the answer

Got it,

SELECT a.dept, count(empid), max(salary)
FROM employee AS A INNER JOIN (SELECT Max(salary) AS MaxSal, dept FROM employee GROUP BY dept) AS dept_MaxSal ON (A.dept=dept_MaxSal.dept) AND (A.Salary=dept_MaxSal.MaxSal)
GROUP BY A.dept;

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Another example.

Code:
```SELECT dept
, max_salary
, count
FROM  (
SELECT dept
, salary
, COUNT(*) AS count
, MAX(salary)
OVER(PARTITION BY dept) AS max_salary
FROM  employee
GROUP BY
dept
, salary
)
WHERE salary = max_salary
ORDER BY
dept DESC
;```
Mimer SQL Developers - Mimer SQL-2003 Validator

Code:
```Result:

The following features outside Core SQL-2003 are used:

T611, "Elementary OLAP operations"
F591, "Derived tables"```
Tested on DB2 9.7.5 on Windows.
Code:
```------------------------------ Commands Entered ------------------------------
WITH
employee(Emp_ID , Emp_Name , Salary , Dept) AS (
VALUES
( 1 , 'Pankaj'  , 1000 , 'Test'       )
, ( 2 , 'Abhey'   , 1000 , 'Test'       )
, ( 3 , 'Prachee' ,  800 , 'Test'       )
, ( 4 , 'Sumeet'  , 1100 , 'Quality'    )
, ( 5 , 'Akshat'  , 1200 , 'Quality'    )
, ( 6 , 'Gaurav'  , 1500 , 'Management' )
, ( 7 , 'Ranjan'  , 1500 , 'Management' )
)
SELECT dept
, max_salary
, count
FROM  (
SELECT dept
, salary
, COUNT(*) AS count
, MAX(salary)
OVER(PARTITION BY dept) AS max_salary
FROM  employee
GROUP BY
dept
, salary
)
WHERE salary = max_salary
ORDER BY
dept DESC
;
------------------------------------------------------------------------------

DEPT       MAX_SALARY  COUNT
---------- ----------- -----------
Test              1000           2
Quality           1200           1
Management        1500           2

3 record(s) selected.```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•