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 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?