SELECT list is not in GROUP BY even after selecting that column issue-CodePudding

I was writing one query in which there is one join and one group by but on executing the query i got this error which is quite common but for reference this was the error

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ipay_app.sp.code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

the query is

select sp.code,count(sps.sp_key) 
from service_providers sp
left join service_providers_sub sps
on sp.code = sps.sp_key
group by sps.sp_key;

but when i am adding sp.code in group by clause i am getting expected result .

just wanted to make sure why ?

CodePudding user response：

Your select has a non-aggregated column (sp.code) and an aggregate function (count(sps.sp_key)). Therefore you need to include the non-aggregated column to GROUP BY:

select sp.code, count(sps.sp_key) 
from service_providers sp
  left join service_providers_sub sps on sp.code = sps.sp_key
group by sp.code;

CodePudding user response：

Try this from your mysql console :

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Then try your query again