Regarding this error:

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'classicmodels.oe.phone' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

When we select the nonaggreated column, it has to be either in group by clause or functionally depend on the group by columns.

What does it mean by a column "functionally depend on" another column?

with oe as (
    select    o.officeCode, o.phone, e.employeeNumber 
    from      offices o 
    left join employees e
           on o.officeCode = e.officeCode
)
select   count(*), phone 
from     oe 
group by officeCode

I use the classicmodels sample database from

The above query is fine, even when I list the column phone, but group by the column officeCode.

But if I do not use the CTE, but:

create table oe as 
    select    o.officeCode, o.phone, e.employeeNumber 
    from      offices o 
    left join employees e
           on o.officeCode = e.officeCode;

and then:

select   count(*), phone 
from     oe 
group by officeCode

I would get the error:

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'classicmodels.oe.phone' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Does it mean that in the above CTE version, column phone is functionally depending on officeCode, so we are ok to list column phone while we group by column officeCode?

What other use cases of a column is functionally depending on another column?

CodePudding user response：

But if I do not use the CTE, but create table... I would get the error

This is because in the new table, officeCode is not defined as unique (by index / primary key), and therefore phone cannot be considered as functional dependent from it.

Does it mean that in the above CTE version, column phone is functionally depending on officeCode, so we are ok to list column phone while we group by column officeCode?

Yes.

What other use cases of a column is functionally depending on another column?

The MySQL reference manual lists several cases at Detection of Functional Dependence:

The following discussion provides several examples [...]:

• Functional Dependencies Derived from Keys
• Functional Dependencies Derived from Multiple-Column Keys and from Equalities
• Functional Dependency Special Cases
• Functional Dependencies and Views
• Combinations of Functional Dependencies

I will not cite the whole article, and invite readers to visit this article in the reference manual.