CODEWITHSUNDEEP
sql
udara Nadeeshani
udara Nadeeshani

Reputation: 41

find max(sum())

create table dept
(
    did char(12) primary key,
    budget float,
    managerId int foreign key references emp
)

I write this query to find maximum sum

SELECT MAX(aa) AS max 
FROM 
    (SELECT SUM(budget) AS aa 
     FROM dept 
     GROUP BY managerId) dept

but I want to find the managerId that who can handle maximum budget.

Upvotes: 0

Views: 515

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42753

select top 1 sum(budget), managerId 
from dept 
group by managerId 
order by sum(budget) desc

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

Just pick these rows where sum(budget) is equal to max. use a subquery to calculate max

SELECT managerId 
FROM dept 
GROUP BY managerId 
HAVING sum( budget ) = (
         SELECT MAX(aa) as max 
         FROM (
            SELECT SUM(budget) AS aa FROM dept GROUP BY managerId
         ) dept
      )

Upvotes: 1

Related Questions