I have been having issues switching to an offline version of the Lahman SQL baseball database. I was using a terminal embed into an EDX course. This command runs fine on the web terminal:
SELECT concat(m.nameFirst,concat(" ",m.nameLast)) as Player, p.IPOuts/3 as IP, p.W,p.L,p.H,p.BB,p.ER,p.SV,p.SO as K, p.IPOuts+p.W*5+p.SV+p.SO-p.BB-p.L-p.H as PTS, p.yearID as Year FROM Pitching p Inner Join Master m ON p.playerID=m.playerID WHERE p.yearID=2014 AND p.IPOuts>=50 GROUP BY m.playerID ORDER BY PTS DESC;
Which is running SQL 5.5.46, but when I use my offline version running 5.7.10 I get the following error code:
Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘stats.m.nameFirst’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
I’ve been reading a lot of solutions to people’s problems, but they haven’t helped in this case. That’s never happened before, so I think this is either super obvious or maybe I’m getting ok at coding. Anyway, anyone know how to fix this?
In 5.7 the sqlmode is set by default to:
To remove the clause ONLY_FULL_GROUP_BY you can do this:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
This supposed you need to make that GROUP BY with non aggregated columns.