SQL - How to sum duplicate count result

Question

I have some problem about my query.

I want to categorize employee location and count it base on region, here my query:

SELECT
    (CASE 
        WHEN location = 'India' THEN 'Asia' 
        WHEN location  = 'Italy' THEN 'Europe' 
        WHEN location  = 'Singapore' THEN 'Asia' 
        WHEN location  = 'Australia' THEN 'Australia' 
        ELSE NULL 
     END) AS Region,  
    COUNT(location) AS Total_person 
FROM
    Location_tbl 
GROUP BY
    location

the result come like this :

1. Asia = 1
2. Europe = 1
3. Asia = 1
4. Australia = 1

The result I want is like this :

1. Asia = 2
2. Europe = 1
3. Australia = 1

Can you guys help me how to fix my query so I can get the result I want.

Answer 1

You need to add the custom group in the group by -

select 
CASE WHEN location in( 'India','Singapore') THEN 'Asia' WHEN location  = 'Italy' THEN 'Europe' WHEN location  = 'Australia' THEN 'Australia' END as Region, 
count(location) as Total_person from Location_tbl 
group by CASE WHEN location in( 'India','Singapore') THEN 'Asia' WHEN location  = 'Italy' THEN 'Europe' WHEN location  = 'Australia' THEN 'Australia' END