Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    chennai
    Posts
    25

    Unanswered: need -> max(sum(salary)) query

    hi

    i have a table employee:

    dept ename salary
    ---- --------- -------
    10 A 2500
    20 B 3500
    30 C 4000
    20 D 5500
    10 E 4500
    30 F 5200


    FIRST QUERY:
    select dept,sum(salary) from employee group by dept

    the above one is working fine..

    after working the first query output,
    i want to select the dept,max(sum(salary)) from the table...

    how?? could any one send me immediately...

    thanks in advance
    Sakthi

  2. #2
    Join Date
    Mar 2004
    Location
    chennai
    Posts
    25

    Re: need -> max(sum(salary)) query

    the below one query will give only the max(sum(salay))

    --------------
    select max(sal) from (select sum(salary) as sal from employee
    group by dept) as a
    ---------------------

    but i need - Which DEPT has the max(sum(salary)) ??


    need these two values:

    dept, max(sum(salary))

    thanks
    Sakthi

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select top 1
           dept
         
    sum(salary)
      
    from employee 
    group 
        by dept
    order
        by sum
    (salarydesc 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2004
    Location
    chennai
    Posts
    25
    hi
    it works .


    thanks rudy.....
    Sakthi

  5. #5
    Join Date
    Feb 2012
    Posts
    2
    select d.DepartmentName as department,SUM(e.Salary) as 'total salary' from Emplyee e inner join Department d on e.DeptId=d.DepartmentID group by d.DepartmentName having sum(e.Salary) = (select max(sal) from (select sum(Salary) as sal from Emplyee group by DeptId) as a)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    umair, that's really quite complicated as compared with post #3 which works just fine

    it's also eight years late

    nice try, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2013
    Posts
    1
    "find the top 10 buying section sum " "having " "max income in between 1mar3012 and 1apr2013"


    table

    buying section date money
    10 400
    10 500
    10 100
    10 1000
    20
    20
    20

  8. #8
    Join Date
    Nov 2013
    Posts
    1
    Quote Originally Posted by r937 View Post
    umair, that's really quite complicated as compared with post #3 which works just fine

    it's also eight years late

    nice try, though
    It might be late and more complicated, but it's more accurate. Post number 3 ignores the case where two or more departments have the same max salary.

Posting Permissions

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