Confused by a Group by with a constant (SQL)

Question

I have seen a query on https://mode.com/sql-tutorial/sql-case/ where within a GROUP BY a constant is used. Here ist the relevant excerpt:

SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY 1

Can someone explain to me why this is working? In my understanding only aggregate functions are allowed in the SELECT clause (or functionally dependent columns - but there are no such columns in this case). However, the CASE statement clearly is not an aggregating function.

Check this answer on an other exchange: [Why do we use Group by 1 and Group by 1,2,3 in SQL query?](https://dba.stackexchange.com/questions/86609/why-do-we-use-group-by-1-and-group-by-1-2-3-in-sql-query) — Stormix, May 04 '23 at 10:03
Which dbms are you using? `GROUP BY 1` isn't valid SQL standard syntax. — jarlh, May 04 '23 at 10:23
Does this answer your question? [What does SQL clause "GROUP BY 1" mean?](https://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean) — bonCodigo, May 04 '23 at 10:27

score 2 · Answer 1 · answered May 04 '23 at 10:14

GROUP BY 1 means GROUP BY the first item mentioned in SELECT. So in your case it means

SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END

In your case GROUP BY 1 saves a mess of repetitive code. It doesn't, however, save time: the query planner knows when items mentioned in GROUP BY clauses are identical to those in SELECT clauses.

Avoid this where possible in your own code, lest the next person to read it needs to ask "what is this GROUP BY 1?" on Stack Overflow, like you did.

Confused by a Group by with a constant (SQL)

1 Answers1