COUNT by column created with CASE statement-CodePudding

I'm successfully getting an output for the CASE statement below; however, I want to ultimately get a COUNT of each Pace value, which is what I created in the CASE statement. How can I do that?

SELECT tempo, popularity,
    CASE 
         WHEN tempo < 40 THEN '1'
         WHEN tempo >= '40' AND tempo < '50' THEN '2'
         WHEN tempo >= '50' AND tempo < '60' THEN '3'
         WHEN tempo >= '60' AND tempo < '70' THEN '4'
         WHEN tempo >= '70' AND tempo < '80' THEN '5'
         WHEN tempo >= '80' AND tempo < '90' THEN '6'
         WHEN tempo >= '90' AND tempo < '100' THEN '7'
         WHEN tempo >= '100' AND tempo < '110' THEN '8'
         WHEN tempo >= '110' AND tempo < '120' THEN '9'
         WHEN tempo >= '120' AND tempo < '130' THEN '10'
         WHEN tempo >= '130' AND tempo < '140' THEN '11'
         WHEN tempo >= '140' AND tempo < '150' THEN '12'
         WHEN tempo >= '150' AND tempo < '160' THEN '13'
         WHEN tempo >= '160' AND tempo < '170' THEN '14'
         WHEN tempo > '170' THEN '15'
         ELSE NULL
    END AS Pace
FROM unpopular_songs 
;

CodePudding user response：

you can try the nested query approach.

the following outer query can be your outer query and the initial query you have written can be used as the inner query.

select pace, count(*) from (<your initial query>) group by pace, order by pace  desc

CodePudding user response：

Ultimately I would avoid sub queries for performance. The below should work

SELECT tempo, popularity,
  CASE 
     WHEN tempo < 40 THEN '1'
     WHEN tempo >= '40' AND tempo < '50' THEN '2'
     WHEN tempo >= '50' AND tempo < '60' THEN '3'
     WHEN tempo >= '60' AND tempo < '70' THEN '4'
     WHEN tempo >= '70' AND tempo < '80' THEN '5'
     WHEN tempo >= '80' AND tempo < '90' THEN '6'
     WHEN tempo >= '90' AND tempo < '100' THEN '7'
     WHEN tempo >= '100' AND tempo < '110' THEN '8'
     WHEN tempo >= '110' AND tempo < '120' THEN '9'
     WHEN tempo >= '120' AND tempo < '130' THEN '10'
     WHEN tempo >= '130' AND tempo < '140' THEN '11'
     WHEN tempo >= '140' AND tempo < '150' THEN '12'
     WHEN tempo >= '150' AND tempo < '160' THEN '13'
     WHEN tempo >= '160' AND tempo < '170' THEN '14'
     WHEN tempo > '170' THEN '15'
     ELSE NULL
END AS Pace
INTO #TEMP1
FROM unpopular_songs

SELECT pace, COUNT(Pace) as count
FROM #TEMP1
GROUP BY pace