SQL Tutorial: Intermediate SQL - SQL CASE

I am currently in the "SQL CASE" section in the "Intermediate SQL" Tutorial. Looking at the subsection titled "Using CASE with aggregate functions", I was able to yield the same results using just a "GROUP BY" statement.

One recommended way of performing an aggregate count function based on the year of the player is below.

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(*) AS count
FROM benn.college_football_players
GROUP BY year_group

However, I got the same results using the code below.

SELECT  year,
        COUNT(*) AS count
FROM benn.college_football_players
GROUP BY year

Is there a disadvantage to the way I wrote my SQL query as opposed to the recommended "CASE" method? Is there an advantage to using the "CASE" method as in the example? Is it because the data is already partitioned neatly into groups of "FR", "SO", "JR", and "SR" and there are no "NULL" values? 

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • If you know your data is clean, then your method is much faster.

    The case statement is only "transforming" and/or "filtering" the input data. before the grouping. Thus if YEAR only has 4 known values then the case statement is pointless, as the 'No Year Data' is needed. As you noted, or if there are Nulls or other unwanted data, I use CASE statements to bucket those into English meaningful sets like 'No Year Data' on the last layer of results (to handle nulls or empty LEFT JOIN type situations)

    So that year was hand entered (aka from humans) I do things like:

    SELECT CASE WHEN trim(lower(year)) = 'fr' THEN 'FR'
                WHEN trim(lower(year)) = 'so' THEN 'SO'
                WHEN trim(lower(year)) = 'jr' THEN 'JR'
                WHEN trim(lower(year)) = 'sr' THEN 'SR'
                ELSE 'No Year Data' END AS year_group,
                COUNT(*) AS count
    FROM benn.college_football_players
    GROUP BY year_group

    to clean-up the data

    Reply Like 1
  • That makes sense and it is along the lines of what I was thinking. Thank you for the clarification and example.

    Reply Like
Like Follow
  • Status Answered
  • 11 mths agoLast active
  • 2Replies
  • 337Views
  • 2 Following