Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

Trying to understand more on the below error:

"Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'companydb.e.dno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

my data set

my resulting table after join => Used, select dno, dnumber, dname, fname, ssn from employee, department where dno=dnumber order by dname;

From what I understood, I can use those columns in select clause which are unique to the set that has been filtered using group by and having clauses. So, here's my confusion.

Below query (to find the department with least number of employees) is failing with the above mentioned error:

select **dno**
from employee e, department d 
where e.dno=d.dnumber 
group by **dname** having count(ssn) =
     (select min(mycount) 
      from (select count(ssn) as mycount 
            from employee
            group by dno
           ) mytable
     );

But the below query (for the same requirement) is successful:

select **dname**
from employee e, department d 
where e.dno=d.dnumber 
group by **dno** having count(ssn) =
     (select min(mycount) 
      from (select count(ssn) as mycount 
            from employee
            group by dno
           ) mytable
     );

And from the data set you can understand that the dno and dname within each group that is formed before having by are unique. So, which is it that i can select dname with group by dno but not dno with group by dname?

Answer 1

Change the SQL Modes:

set global sql_mode='';
set global sql_mode='STRICT_TRANS_TABLES';

Answer 2

I do lot of research about this error and got solution that if you are select timestamp within DATE then you have to use this symbol (``) before and after of column name. select DATE(addedon) as addedon, count(*) from data where id= '$bid' GROUP BY DATE(addedon)

Answer 3

MySQL has to decide before looking at the actual data if the value in your select list is unambiguous. It is a syntax error, not a value error.

According to the documentation, Mysql will

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

"Functionally dependent on the group by columns" is a fancy way of saying: if you know the value of the grouped column, you know the value refered to in the select list without any doubt. And that is what you are saying: you assume that if you know dname, you know dno without any doubt.

But currently, nothing in your datamodel stops you from e.g. giving every department the same name. And as long as you can do it, MySQL has to be prepared for it (and has to reject your query, as it does not check the data). To tell MySQL that you will not/can not do that, you can define dname as unique not null. Then dnumber/dno is "uniquely determined" by dname, and select dno ... group by dname should work.

Your second query, select dname ... group by dno (which is equivalent to select dname ... group by dnumber), works because it is the primary key (and thus every value in the row is functionally dependent on it): if you know dnumber/dno, there is no ambiguity about the value of dname.

If you do not want to add that constraint (but are 100% certain that it is fulfilled, including null), you can also replace select dno ... group by dname with select dno ... group by dno. If dname actually is as unambiguous as you assume, this is logically equivalent (as every dname has one dno and every dno has one dname). For the same reason, you can simply use group by dno, dname in both cases. Alternatively, you can use e.g. select max(dno) ... group by dname, which also has no effect on the result, since max() of a distinct value is just that value.

All those means are used to inform MySQL that it can find an unambiguous value in any case, and before looking at the actual data. If your assumption is correct, it does not change the outcome (although it may have an effect on the execution plan). If it changes the resultset, your assumption wasn't correct.

Answer 4

Explored the other options and came up with the below:

The following rules apply for the columns (other than the group by column(s)) to be used in select list:

Case 1: Select involving single table

1) group by col1 => col1 has to be unique & not null or the primary key column of the corresponding table

         select super_ssn from employee ... group by lname => Error

         select super_ssn from employee ... group by lname (if lname is unique not null) => Success

         select mgr_start_date from department ... group by dname => Error

         select mgr_start_date from department ... group by dname (if dname is unique not null) => Success

2) group by col1, col2 ...  => One of col1, col2 ... should be unique not null or primary key column of the corresponding table

         select super_ssn from employee ... group by sex, lname => Error

         select super_ssn from employee ... group by sex, lname (if lname is unique not null) => Success

         select mgr_start_date from department ... group by mgr_ssn, dname => Error

         select mgr_start_date from department ... group by mgr_ssn, dname (if dname is unique not null) => Success

Case 2: Select involving multiple tables

1) group by col1 => Only columns from the same table can be used in select list if col1 is unique & not null or the primary key column of the corresponding table

         select super_ssn from employee, department ... group by ssn => Success

         select super_ssn from employee, department ... group by dnumber => Error

         select mgr_start_date from employee, department ... group by dnumber => Success

         select mgr_start_date from employee, department ... group by ssn => Error

2) group by col1, col2 ...  =>

     a) Only columns from the same table as one of col1, col2 ... can be used in select list if col1/col2 ... is unique & not null or primary key column of the corresponding table

         select super_ssn from employee, department ... group by ssn, dname => Success

         select super_ssn from employee, department ... group by lname, dname => Error

         select mgr_start_date from employee, department ... group by dnumber, lname => Success

         select mgr_start_date from employee, department ... group by dname, lname => Error

     b) Any column of tany able can be used if one of the group by colums is the primary of any table. This doesn't apply to the group by primary key column is part of the join condition

         Ex 1: ssn even though primary key column of employee table, it can be used to select any column in any table

         select mgr_start_date from department, employee ... group by ssn => Success

          Ex 2: Dnumber even though primary key column of department table, it can't be used to select any column in any table since it is part of the join condition

          select super_ssn from department, employee ... group by dnumber => Error