CODEWITHSUNDEEP
sqloracle-databasegroup-by
forgottofly
forgottofly

Reputation: 2717

Get maximum value of grouped by departments using SQL

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

Upvotes: 2

Views: 184

Answers (3)

Donat
Donat

Reputation: 4813

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.

Upvotes: 1

Oiale
Oiale

Reputation: 434

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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);

Upvotes: 2

Related Questions