Get maximum value of grouped by departments using SQL

user_4060595 0
Viewed 89

I'm trying to view the maximum salary from the table ORG grouped by DEPT .Below is my table data I've created using Oracle SQL

enter image description here

I've used the query to group and display DEPT and total salary as shown below where I got the grouped department and its's corresponding total salary

QUERY :SELECT DEPT,SUM(SALARY) FROM ORG GROUP BY DEPT;

RESULT :

enter image description here

But I'm expecting the result only to display the maximum value of the above ie., 2 | HW | 113000 .Can you pls help to modify the above query..Thanks

group-by oracle sql
3 Answers

You seem to want the whole row, not an aggregation. So, use a where clause, in this case, with a correlated subquery:

select o.*
from org o
where o.salary = (select max(o2.salary) from org o2 where o2.dept = o.dept);
user_11440350

If you need only 1 row from your aggregated query you can use subquery to get it.

SQL> select * 
2 from (
3       select dept,SUM(salary) sum_salary 
4       from org 
5       group by dept
6       order by sum_salary desc
7      )
8 where rownum <= 1

DEPT       SUM_SALARY
---------- ---------- 
  HW       113000
user_62157740

Your query is already close to the solution. You need only get the row with the maximum value for salary:

WITH SUMSAL AS (SELECT DEPT, SUM(SALARY) AS SAL FROM ORG GROUP BY DEPT)
SELECT * FROM SUMSAL 
WHERE SAL = (SELECT MAX(SAL) FROM SUMSAL)

This may give you more than one row, if there is more than one department having the maximum sum of salaries.

user_105330110
Related