MySQL case when count distinct

I’m trying to get counts for distinct videos by viewership tier. I have the following table:

vid_id	views
1	    6
1	    10
1	    900
2	    850
2	    125000
3	    1010
3	    12239
3	    150000

I tried using this code to get my desired output:

SELECT
	CASE
	    WHEN views < 1000 THEN '< 1K Views'
            WHEN views >= 1000 AND views < 10000 THEN '1K to 10K Views'
            WHEN views >= 10000 AND views < 100000 THEN '10K to 100K Views'
            WHEN views >= 100000 AND views < 1000000 THEN '100K to 1M Views'
            ELSE '1M+ Views'
	END AS tier,
	COUNT(distinct vid_id)
FROM
	test
GROUP BY
	tier

Since there are multiple entries for each vid_id, I want to group the counts of the videos for each tier using the max number of views for each vid_id. So my output should be:

tier	            COUNT(views)
< 1K Views	        1 
100K to 1M Views	2 

Video #1 reached 900 views max, so it’s in the < 1K Views tier. Videos 2 and 3 reached 125,000 and 150,000 views, respectively. So they are in the 100K to 1M Views tier.

1 Like

Hey, Justin. Great to see you here!

I would first filter the starting table for the rows that match the maximum for each video, i.e., I’d obtain a table that looks like this:

vid_id	views
1	900
2	125000
3       150000

Then you can use the code in your question on this table. So how do we get a table that looks like this?

Expand to see a solution.
SELECT T1.*
  FROM test as T1
  LEFT JOIN (SELECT vid_id, MAX(views) as max_views
               FROM test
              GROUP BY 1
            ) AS T2
    ON T1.vid_id = T2.vid_id and T1.views = T2.max_views
;

This is untested code. Let me know if it works!


Edit: Han’s observations are absolutely right, but they don’t take away from the main point of my answer. You just need to tweak it like that Han described.

1 Like

Hi Justin if you look at sql order of execution: https://sqlbolt.com/lesson/select_queries_order_of_execution
You can see FROM and GROUP BY come before select. The GROUP BY you currently have will not recognize what “tier” means because it’s undefined yet.
You can wrap the whole SELECT statement in (), treat this as a new table, and write another select from (newtable) groupby over it. Alternatively you can avoid nesting by abstracting out the nested SELECT using CREATE VIEW to improve readability
https://www.techonthenet.com/mysql/views.php

1 Like

Thanks, Bruno! I’ll give this a try. I found an alternate solution that seems to get the job done well.

Thanks, @hanqi! I’ll give your approach a try too.