how to mysql count case when

Question

hi I'm get the department name by group by and count the people in that department. I would like to call and count departments other than a specific department as 'Other'. Is it possible to display in a single column called COUNT using a case when?

Answer 1

Put the CASE in the SELECT list, and then group by the alias you assign there.

SELECT CASE title_name 
        WHEN 'Engineer' THEN title_name
        ELSE 'OTher'
       END AS title,
    COUNT(*) AS total_count
FROM mrpa_user
WHERE title_name NOT IN ('NULL', '.')
GROUP BY title