Counting Distinct Values Across Multiple Columns MySQL

In some scenarios, we may need to count distinct values based on more complex conditions that can’t be easily handled with just the WHERE clause.  

SQL’s CASE statement allows us to apply conditional logic within our queries, enabling us to create more dynamic counts based on specific criteria.

Let’s say we want to count how many distinct courses are active and how many are inactive. We can use CASE to handle this logic:

 SELECT
    COUNT(DISTINCT CASE WHEN is_active = 'TRUE' THEN id END) AS active_courses,
    COUNT(DISTINCT CASE WHEN is_active = 'FALSE' THEN id END) AS inactive_courses
FROM
    Course;

 
OR

SELECT
    COUNT(DISTINCT CASE WHEN status = 'active' THEN userid END) AS active_users
FROM
    your_table;