MySQL GROUP BY with specific value and remaining as 'other'-CodePudding

I have a table "Staff" it has a column "staffNationality" with all the countries of staffs. I want a query to count the staffs and group the nationalities according to ONE country (for example: France) and count all other countries as "Other". Instead of showing the count for each country.

like this:

staffCount	staffNationality
4	France
10	Other

CodePudding user response：

You may use conditional aggregation here:

SELECT
    CASE StaffNationality WHEN 'France'
                          THEN 'France' ELSE 'Other' END AS StaffNationality,
    COUNT(*) AS staffCount
FROM yourTable
GROUP BY 1;

CodePudding user response：

try this

select count(1) as staffCount,case when nationalities = 'France' then 'France' else 'Other' end as staffNationality 
from Staff s
join staffNationality sn on s.staffid = sn.staffid