CODEWITHSUNDEEP
mysqlsql
ProgrammerDummy
ProgrammerDummy

Reputation: 57

How can I get to top query of my result sumOfSalary

SELECT 
FORMAT(SUM(e.salary),0) as sumOfSalary, 
d.departName 
FROM
employee e 
JOIN depart d on (d.id = e.depart) 
GROUP BY d.departName
ORDER BY sumOfSalary desc

How can I return the highest value result from sumOfSalary without using LIMIT 1? im trying to use MAX() in SUM(e.salary) but it doesnt help I get an error #1111 - Invalid use of group function

Upvotes: 1

Views: 764

Answers (3)

Laurent PELE
Laurent PELE

Reputation: 487

as you use format, your result are not sorted by numeric value but alphabetical order so '600' comes before '3000' in descending order

    select departName, format(max(sumOfSalary),0) from 
 (SELECT 
    SUM(e.salary) as sumOfSalary, 
    d.departName 
    FROM
    employee e 
    JOIN depart d on (d.id = e.depart) 
    GROUP BY d.departName
    ORDER BY SUM(e.salary) desc limit 1) as nested

Upvotes: 0

Ildar Akhmetov
Ildar Akhmetov

Reputation: 1431

If you need just the max salary among all departments (I hope that I got your question correctly!), then use a nested query:

SELECT FORMAT(MAX(sumOfSalary),0) FROM (
SELECT 
SUM(e.salary) as sumOfSalary, d.departName 
FROM employee e 
JOIN depart d on d.id = e.depart)
GROUP BY d.departName
) AS ss

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

Use LIMIT to return only the top result of the query:

SELECT 
    FORMAT(SUM(e.salary),0) as sumOfSalary, 
    d.departName 
FROM
    employee e 
JOIN depart d 
    on (d.id = e.depart) 
GROUP BY d.departName
ORDER BY sumOfSalary desc
LIMIT 1

Upvotes: 1

Related Questions