sql - Why does Case written this way bring a 'ghost' row in my table?

Question

Question: Write a query that calculates the combined weight of all underclass players (FR/SO) in California as well as the combined weight of all upperclass players (JR/SR) in California.

My Answer:

SELECT
  CASE
    WHEN state = 'CA' AND year IN ('FR', 'SO')
    THEN 'underclass'
    WHEN state = 'CA' AND year IN ('JR', 'SR')
    THEN 'upperclass'
    ELSE NULL
  END AS class_divide,
  SUM(weight) AS combined_weights
FROM benn.college_football_players
GROUP BY 1

Results:

| class_divide | combined_weights |
+--------------+------------------+
|              | 5326369.0        |
| upperclass   | 262452.0         |
| underclass   | 274374.0         |

Correct Answer:

SELECT
  CASE
    WHEN year IN ('FR', 'SO')
    THEN 'underclass'
    WHEN year IN ('JR', 'SR')
    THEN 'upperclass'
    ELSE NULL
  END AS class_group,
  SUM(weight) AS combined_player_weight
FROM benn.college_football_players
WHERE state = 'CA'
GROUP BY 1

Why does it work to add

Where state = 'CA'

after the CASE and not within the CASE? Thanks in advance.

Bonus question: Is naming columns using numbers i.e Group by 1 a feature unique to PostgreSQL?

标签： sqlpostgresql

Answer 1

The CASE expression does not filter out any rows. So the non-CA rows are all grouped into the NULL group.

Note that ELSE NULL is redundant, because that is the default if nothing matches.

As for GROUP BY 1 . . . This is definitely not standard syntax but most modern databases seems to support it. Of course, most modern databases also use Postgres as their code base, because the Postgres license allows its code base to be incorporated into paying products.