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: 

 

SELECT 
  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,
  COUNT(2)
  FROM benn.college_football_players
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?

Thanks!

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • SELECT 
       CASE WHEN state IN('CA', 'OR', 'WA') THEN 'west_coast'
           WHEN state = 'TX' THEN 'texas'
           ELSE 'Other' END AS player_region,
      SUM(CASE WHEN weight >= 300 THEN 1
        ELSE 0 END) AS count
      FROM benn.college_football_players
      GROUP BY 1
    hope this helps

    Reply Like
Like Follow
  • 3 days agoLast active
  • 1Replies
  • 11Views
  • 2 Following