Learn SQL Database Programming

Understanding how GROUP BY works without aggregate functions

Let's say you want to find the distinct list of player ID's and the teams they've played for. You can execute a query like the following:

USE lahmansbaseballdb;SELECT playerid, teamid FROM battingGROUP BY teamid; 

The previous query will give you this error: Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lahmansbaseballdb.batting.playerID,' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.

This error means that you need to place playerid in the GROUP BY clause, as follows:

USE lahmansbaseballdb;SELECT playerid, teamid FROM battingGROUP BY teamid, ...