Do Columns in CASE functions have to be used in aggregate functions or GROUP BY?
I have been going through the Intermediate SQL tutorial, and in the CASE section, one question asks:
"Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (Everywhere else)."
Here is what I wrote:
CASE WHEN state IN('CA', 'OR', 'WA') THEN 'west_coast'
WHEN state = 'TX' THEN 'texas'
ELSE 'Other' END AS player_region,
CASE WHEN weight >= 300 THEN '300lb+'
ELSE NULL END AS weight_class,
GROUP BY 1
I understand that it is less complicated and overall better to use a WHERE statement to look at football players that weight 300lb+, but why is this incorrect?
The error message I get is:
column "college_football_players.weight" must appear in the GROUP BY clause or be used in an aggregate function
Do the columns in CASE functions (in this case the weight column) have to be used in aggregate functions or GROUP BY? If so, can I change anything such that I solve the problem without using a WHERE statement?