GROUP BY in MySQL and Oracle

In standard SQL if a query is having GROUP BY clause

Then in the select part we cannot have columns that are not present in group by clause. If we want to include columns which are not a part of group by then we should include it as a parameter of Aggregate functions

→ This query is illegal

We are grouping by o.custid

In select clause we have o.custid, c.name ,MAX(o.payment)

1)o.custid → fine , since its present in group by

2)MAX(o.payment) → fine ,we can have aggregate function over columns that are not part of group by clause

3)c.name → this column does not satisfy above two conditions

Because of which the query is illegal

Oracle implements the standard SQL

MySQL extends the standard SQL use of GROUP BY so that we can include columns which are not part of a group by clause or aggregate functions.

But this can lead to non deterministic behaviour

If we have a query like this

According to standard SQL it is illegal but MySql allows it

And in customers table we have a record like this

Shown below

We are grouping by state

Which value should MySQL choose for non aggregated columns

Should it be from record 1 or record 2

It means random, but worse. If the server chooses random values, that implies it would return different values in different queries, so you have a chance of catching the problem when you test your software. But nondeterministic in this context means the server chooses the same value every time, until it doesn’t.

Why might it change the value it chooses? A server upgrade is one reason. A change to table size might be another. The point is, the server is free to return whatever value it wants.

However, this is useful primarily when all values in each non-aggregated column not named in the GROUP BY are the same for each group.

Example shown below

If we group by state

Then whatever record MySQL chooses we don’t have any issues

As records under “AL” are exactly same

If we want to enable SQL standards in MySQL then we can do

SET SESSION SQL_MODE=’ONLY_FULL_GROUP_BY’

Then on executing the query we get following error message

select birth_date,phone,address,

city,state,points from customers

group by state

LIMIT 0, 1000 Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sql_store.customers.birth_date’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by