CODEWITHSUNDEEP
mysqlsqlphpmyadmineasyphp
Elliott Addi
Elliott Addi

Reputation: 380

SQL unexpected return

So basically, I have a simple Database with only one table (it's a test DB). The table has 4 columns:

What I'm trying to do is pretty simple (that's why i'm asking for your help): I want to get the name and average of the student whith the highest average.

What i tried:

SELECT nom, MAX(avg) 
    FROM ( 
        SELECT nom, (noteOrale + noteEcrit)/2 as avg 
        FROM etudiant 
        GROUP BY nom) AS Table;

After trying this query, it returned me the name and an average but the average doesn't correspond to the name.

Can someone give me pointers or explain what went wrong? Thanks

Upvotes: 0

Views: 156

Answers (4)

spencer7593
spencer7593

Reputation: 108450

What's wrong is that the value returned for nom is indeterminate. The MAX() aggregate is causing the query to collapse all of the rows, and picks out the the highest value of avg. That part is working.

But the value returned for nom is from some row in the collapsed group, not necessarily the row that has the highest value of avg. The query basically told MySQL to return a value of nom from any row in the group.

Other databases would throw an error with the query, with an error message along the lines of "non-aggregate in SELECT list not in GROUP BY".

A non-standard MySQL-specific extension allows the query to run. (It is possible to get MySQL to follow the standard more closely, like other database, if we include ONLY_FULL_GROUP_BY in sql_mode. With the extension disabled, MySQL would behave like other databases, and return an error.)

That's the reason for the "unexpected" behavior you observe.

(This answers the question you asked... explaining what went wrong.)

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146541

Just list the rows where the sum of the two fields is equal to the higest sum of the two fields in all the rows. You don't need to divide by two . That's just scaling.

Select * from table
Where noteOrale + noteEcrit =
   (Select Max(noteOrale + noteEcrit)
    From table)

if you also want the computed avg in the output, add it to the select clause.

Select nom, noteOrale, noteEcrit,
    (noteOrale + noteEcrit)/2 Avg
from table
Where noteOrale + noteEcrit =
   (Select Max(noteOrale + noteEcrit)
    From table)

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Use order by and limit. No subquery is necessary:

    SELECT nom, (noteOrale + noteEcrit)/2 as avg 
    FROM etudiant 
    ORDER BY avg DESC
    LIMIT 1;

It would appear that no GROUP BY is needed either, because the values are all on one row.

If they are multiple rows, then you need GROUP BY.

Upvotes: 2

sgeddes
sgeddes

Reputation: 62851

I would just use limit for this:

SELECT nom, avg
FROM (
    SELECT nom, (noteOrale + noteEcrit)/2 as avg 
    FROM etudiant 
    GROUP BY nom
) t
ORDER BY avg DESC
LIMIT 1

mysql allows you to use aggregation without including all non-aggregated columns in the group by clause -- so your query is just returning an arbitrary value for name.

Upvotes: 1

Related Questions