COUNT function

Hey everyone,

 

I'm new to the community and just beginning my SQL education.  Using the "Billboard top 100" data set in the "Mode Public Warehouse" I wrote the following query to see which how many times each music group showed up on the Billboard Charts over the years.


WITH t1 AS (SELECT DISTINCT(year) as year, "group" AS group_name, song_name
FROM tutorial.billboard_top_100_year_end
ORDER BY 1, 2)

SELECT group_name, COUNT(year) AS frequency_on_list, AVG(year) AS time_period
FROM t1
GROUP BY 1
ORDER BY 2 DESC;

 

I'd like to add an additional query to count how many groups made it onto the Billboard list a specific number of times (once, 2-5 times, 6-10 times, etc.) but I really can't wrap my head around it.  I feel like it should definitely involve the COUNT function, and maybe the CASE function? 

 

Any help would be greatly appreciated. Thanks!

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Brian
    • Brian
    • 9 mths ago
    • Reported - view

    After a night of sleeping on it, I came up with the following query to answer the above question.  However, I still wonder, is there a simpler way to write this, perhaps using only 2 queries instead of 3?


      WITH t1 AS (SELECT DISTINCT(year) as year, "group" AS group_name, song_name
      FROM tutorial.billboard_top_100_year_end
      ORDER BY 1, 2),

      t2 AS (SELECT group_name, COUNT(year) AS frequency_on_list,
      CASE WHEN COUNT(YEAR) < 2 THEN 'One Hit Wonder'
      WHEN COUNT(year) BETWEEN 2 AND 5 THEN '2-5'
      WHEN COUNT(year) BETWEEN 6 AND 10 THEN '6-10'
      WHEN COUNT(year) BETWEEN 11 AND 20 THEN '11-20'
      WHEN COUNT(year) > 21 THEN 'Over 21'
      END AS star_power

      FROM t1
      GROUP BY 1
      ORDER BY 2 )

    SELECT COUNT(*) AS number_of_groups, star_power AS appearances_on_billboard
    FROM t2
    GROUP BY 2
    ORDER BY 1

    Reply Like
  • Hi Brain,

    Have no clue as to the shape of the 'tutorial.billboard_top_100_year_end' table

    but assume it's a row per week, and the distinct(year) is to reduce this down to one, but that will not work if the hit was in the charts over the December/January period. This will be you count(year) as frequency_on_list worse...

    I would suggest changing to group_name,song_name as the distinct keys, thus the first query becomes:

    WITH t1 AS (
        SELECT "group" as group_name
            ,song_name
            ,MIN(YEAR) as first_year
            ,MAX(YEAR) as last_year
        FROM tutorial.billboard_top_100_year_end
        GROUP BY 1, 2
    )
    SELECT group_name
        ,COUNT(song_name) AS frequency_on_list
        ,MIN(first_year) as time_start
        ,MAX(last_yeat) as time_end
        ,AVG(firstyear) AS time_period
    FROM t1
    GROUP BY 1
    ORDER BY 2 DESC;
    

    which be simplified (albeit with different meaning of the "time_period" column

    
    SELECT "group" as group_name,
        ,COUNT(DISTINCT(song_name)) AS frequency_on_list
        ,MIN(first_year) as time_start
        ,MAX(last_yeat) as time_end
        ,AVG(firstyear) AS avg_time -- slietly different from you anwser
    FROM tutorial.billboard_top_100_year_end
    GROUP BY 1
    ORDER BY 2 DESC;
    

    then using that you final result can be done like:

    SELECT star_power AS appearances_on_billboard, COUNT(*) AS number_of_groups
    FROM (
        SELECT "group" as group_name,
            ,COUNT(DISTINCT(song_name)) AS frequency_on_list
            ,CASE WHEN frequency_on_list < 2 THEN 'One Hit Wonder'
                WHEN frequency_on_list BETWEEN 2 AND 5 THEN '2-5'
                WHEN frequency_on_list BETWEEN 6 AND 10 THEN '6-10'
                WHEN frequency_on_list BETWEEN 11 AND 20 THEN '11-20'
                WHEN frequency_on_list > 21 THEN 'Over 21'
            END AS star_power
        FROM tutorial.billboard_top_100_year_end
        GROUP BY 1
    )
    ORDER BY 2;
    

    or the sub select can be a CTE like you have used in your examples, and if your DB does allow using of ALIAS rows in the same sub-select you can unroll the use of the ALIAS like you had in your examples OR turn that into a sub-select/CTE

    Reply Like
Like Follow
  • 9 mths agoLast active
  • 2Replies
  • 208Views
  • 2 Following