CODEWITHSUNDEEP
mysqlsqlsummax
Heidi
Heidi

Reputation: 95

sql - Why doesn't MAX() of SUM() work?

I am trying to understand why the SQL command of MAX(SUM(col)) gives the a syntax error. I have the two tables as below-:

+--------+--------+---------+-------+
| pname  | rollno | address | score |
+--------+--------+---------+-------+
| A      |      1 | CCU     |  1234 |
| B      |      2 | CCU     |  2134 |
| C      |      3 | MMA     |  4321 |
| D      |      4 | MMA     |  1122 |
| E      |      5 | CCU     |  1212 |
+--------+--------+---------+-------+

Personnel Table

+--------+-------+----------+
| rollno | marks | sub      |
+--------+-------+----------+
|      1 |    90 | SUB1     |
|      1 |    88 | SUB2     |
|      2 |    89 | SUB1     |
|      2 |    95 | SUB2     |
|      3 |    99 | SUB1     |
|      3 |    99 | SUB2     |
|      4 |    82 | SUB1     |
|      4 |    79 | SUB2     |
|      5 |    92 | SUB1     |
|      5 |    75 | SUB2     |
+--------+-------+----------+

Results Table

Essentially I have a details table and a results table. I want to find the name and marks of the candidate who has got the highest score in SUB1 and SUB2 combined. Basically the person with the highest aggregate marks.

I can find the summation of SUB1 and SUB2 for all candidates using the following query-:

select p.pname, sum(r.marks) from personel p, 
result r where p.rollno=r.rollno group by p.pname;

It gives the following output-:

+--------+--------------+
| pname  | sum(r.marks) |
+--------+--------------+
| A      |          178 |
| B      |          167 |
| C      |          184 |
| D      |          198 |
| E      |          161 |
+--------+--------------+

This is fine but I need the output to be only D | 198 as he is the highest scorer. Now when I modify query like the following it fails-:

select p.pname, max(sum(r.marks)) from personel p, 
result r where p.rollno=r.rollno group by p.pname;

In MySQL I get the error of Invaild Group Function.

Now searching on SO I did get my correct answer which uses derived tables. I get my answer by using the following query-:

SELECT 
    pname, MAX(max_sum)
FROM
    (SELECT 
        p.pname AS pname, SUM(r.marks) AS max_sum
    FROM
        personel p, result r
    WHERE
        p.rollno = r.rollno
    GROUP BY p.pname) a;

But my question is Why doesn't MAX(SUM(col)) work ?

I don't understand why max can't compute the value returned by SUM(). Now an answer on SO stated that since SUM() returns only a single value so MAX() find its meaningless to compute the value of one value, but I have tested the following query -:

select max(foo) from a;

on the Table "a" which has only one row with only one column called foo that holds an integer value. So if MAX() can't compute single values then how did this work ?

Can someone explain to me how the query processor executes the query and why I get the error of invalid group function ? From the readability point of view using MAX(SUM(col)) is perfect but it doesn't work out that way. I want to know why.

Are MAX and SUM never to be used together? I am asking because I have seen queries like MAX(COUNT(col)). I don't understand how that works and not this.

Upvotes: 0

Views: 2016

Answers (4)

Tanzira Najnin Tonu
Tanzira Najnin Tonu

Reputation: 100

with temp_table (name, max_marks) as
(select name, sum(marks) from personel p,result  r, where p.rollno = r.rollno group by p.name)
select *from temp_table where max_marks = (select max(max_marks) from temp_table);

I didn't run this. But try this one. Hope it will work :)

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191779

The error is 1111: invalid use of group function. As for why specifically MySQL has this problem I can really only say it is part of the underlying engine itself. SELECT MAX(2) does work (in spite of a lack of a GROUP BY) but SELECT MAX(SUM(2)) does not work.

This error will occur when grouping/aggregating functions such as MAX are used in the wrong spot such as in a WHERE clause. SELECT SUM(MAX(2)) also does not work.

You can imagine that MySQL attempts to aggregate both simultaneously rather than doing things in an order of operations, i.e. it does not SUM first and then get the MAX. This is why you need to do the queries as separate steps.

Upvotes: 1

John Bollinger
John Bollinger

Reputation: 180998

Aggregate functions require an argument that provides a value for each row in the group. Other aggregate functions don't do that.

It's not very sensical anyway. Suppose MySQL accepted MAX(SUM(col)) -- what would it mean? Well, the SUM(col) yields the sum of all non-NULL values of column col over all rows in the relevant group, which is a single number. You could take the MAX() of that to be that same number, but what would be the point?

Your approach using a subquery is different, at least in principle, because it aggregates twice. The inner aggregation, in which you perform the SUM(), computes a separate sum for each value of p.pname. The outer query then computes the maximum across all rows returned by the subquery (because you do not specify a GROUP BY in the outer query). If that's what you want, that's how you need to specify it.

Upvotes: 4

Jim Mc
Jim Mc

Reputation: 346

Try something like this:

select max(rs.marksums) maxsum from
(
select p.pname, sum(r.marks) marksums from personel p, 
result r where p.rollno=r.rollno group by p.pname
) rs

Upvotes: 1

Related Questions