Count of Max(Salary) for each Department

mjmzafaohoujb · #1 (**permalink**) 10-14-11, 04:29

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

mjmzafaohoujb · #2 (**permalink**) 10-14-11, 05:32

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;

tonkuma · #3 (**permalink**) 02-07-12, 07:02

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech