Using group by in mysql, cause error 1055

Question

when I'm trying to use mysql to select the first form into the second form

select id, name ,
(case when years=1992 then cost else 0 end) as year1992,
(case when years=1993 then cost else 0 end) as year1993,
(case when years=1994 then cost else 0 end) as year1994
from pivot 
group by name;

I got a strange error

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nctest.pivot.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What's the problem here?

-------------update--------------------------

I find I make a mistake using the group by.

select id, name ,
sum(case when years=1992 then cost else 0 end) as year1992,
sum(case when years=1993 then cost else 0 end) as year1993,
sum(case when years=1994 then cost else 0 end) as year1994
from pivot 
group by id, name;

then it works;;

Why are you trying to pivot the data in MySQL? Your application should be able to use the original form quite happily — eggyal
@eggyal it's the old database, we are migrating the old database into the new database,so it's a history problem.. — user4985526

Marco Ferrari Marco Ferrari · Accepted Answer · 2016-01-12T12:08:28

You are trying to SELECT the id column but you can't because it's not in the GROUP BY expression.

Using group by in mysql, cause error 1055

1 Answers