Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Question 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. #2
    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. #3
    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.

Tags for this Thread

Posting Permissions

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