tyan tyan - 1 month ago 5
MySQL Question

Using group by in mysql, cause error 1055

enter image description here

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

Answer

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