-1

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;

image one before adding group by

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

image two after adding group by

just wanted to make sure why ?

Rohannn Singh
  • 56
  • 1
  • 7
  • 1
    In your query it is mandatory to group by `sp.code` if it is part of the select list and not part of an aggregate function. – Ergest Basha Nov 14 '22 at 11:02
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) – philipxy Nov 14 '22 at 11:56
  • [Error related to only_full_group_by when executing a query in MySql](https://stackoverflow.com/q/34115174/3404097) – philipxy Nov 14 '22 at 23:07

1 Answers1

2

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;
slaakso
  • 8,331
  • 2
  • 16
  • 27