SQL: Combining Summation Functions and Maximum Population

Question

How to get the maximum 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 |
+------------+

I want to:

|      55.00 |
|      55.00 |

Thanks in advance.

Answer 1

Use Havingparagraph and Sub-query. Something like that

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’s really easy in SQL SERVERwhere we have TOP 1 with Tiesthat avoidsSub-query

Answer 2

If you just need the maximum value, then one line will do:

select sum(hours)
from works_on
group by pno
order by sum(hours) desc
limit 1;

I'm not sure why you need two lines, but then Prdp's answer is correct.

Answer 3

, ( )  

Select Max(sumHrs)
From (Select sum(hours) sumHrs
      From works_on 
      gtoup by pno) z

I wonder what is the value of returning multiple rows from a result set, which is already an aggregation, and not raw data rows. If your desired results also included a value pno, then I would understand (you want to know which one pnohas the greatest amount of hours), but just getting the maximum amount of hours doesn't seem to matter much, Or did you just leave part of the problem, just for simplicity?