How do I fix this to just show the count for the 4 specified buckets?-CodePudding

I am trying to group the movie runtimes and get the counts for 4 buckets. My query is showing me results in 2 columns but is somehow separating and repeating the bucket names. How do I fix this to just give me the total counts in each group?

SELECT 
      CASE WHEN runtime >= 0 AND runtime <= 60 THEN '0-60'
           WHEN runtime > 60 AND runtime <= 120 THEN '61-120'
           WHEN runtime > 120 AND runtime <= 180 THEN '121-180'
           ELSE 'Over 180'
      END runtime_range,
      count(*) as Movie_Runtime
FROM movies
GROUP BY runtime;

data output

CodePudding user response：

You need to group by your buckets, not by the value you're dividing into buckets. You could do something like this:

SELECT 
      CASE WHEN runtime >= 0 AND runtime <= 60 THEN '0-60'
           WHEN runtime > 60 AND runtime <= 120 THEN '61-120'
           WHEN runtime > 120 AND runtime <= 180 THEN '121-180'
           ELSE 'Over 180'
      END runtime_range,
      count(*) as Movie_Runtime
FROM movies
GROUP BY 
      CASE WHEN runtime >= 0 AND runtime <= 60 THEN '0-60'
           WHEN runtime > 60 AND runtime <= 120 THEN '61-120'
           WHEN runtime > 120 AND runtime <= 180 THEN '121-180'
           ELSE 'Over 180'
      END;

Or you could use a CTE, something like

WITH groups AS (
SELECT 
      CASE WHEN runtime >= 0 AND runtime <= 60 THEN '0-60'
           WHEN runtime > 60 AND runtime <= 120 THEN '61-120'
           WHEN runtime > 120 AND runtime <= 180 THEN '121-180'
           ELSE 'Over 180'
      END runtime_range
FROM movies
)
SELECT 
    runtime_range,
    count(*) as Movie_Runtime
FROM ranges
GROUP BY runtime_range;