Tehreem Tehreem - 2 months ago 12
MySQL Question

SQL: Combining sum and max aggregate function

How do I get the max value from the following query:

select sum(hours) from works_on group by pno;
+------------+
| sum(hours) |
+------------+
| 52.50 |
| 50.00 |
| 55.00 |
| 25.00 |
| 55.00 |
+------------+


What I want is:

| 55.00 |
| 55.00 |


Thanks in advance.

Answer

Use Having Clause and Sub-query. Something like this

SELECT Sum(hours) 
FROM   works_on 
GROUP  BY pno 
HAVING Sum(hours) = (SELECT Sum(hours) h 
                     FROM   works_on 
                     GROUP  BY pno 
                     ORDER  BY h DESC 
                     LIMIT  1) 

But it is really easy in SQL SERVER where we have TOP 1 with Ties which avoids the sub-query

Comments