How to get the agg values in SQL-CodePudding

I've a sample table

city     member    job_status
NY       John      not_active
NY       Sam       not_active
NY       Phil      active
WDC      Martha    active
WDC      Harvey    not_active

How can I get a summary table like

city    total_members    total_not_active
NY        3                  2
WDC       2                  1

select city, count(member) total_members from sample group by city

I could use the above one, but how can get total_not_acitve?

CodePudding user response：

SELECT city, COUNT(member) total_members, SUM(CASE
WHEN job_status = 'not_active' THEN 1 
ELSE 0 
END) AS not_active
FROM Member
GROUP BY city

CodePudding user response：

use this

select city, count(member) total_members,
sum(case when job_status = 'not_active' then 1 else 0 end) as total_not_active 
from sample 
group by city

CodePudding user response：

You can use CONDITIONAL AGGREGATION:

SELECT 
    city, 
    COUNT(member) AS cn,
    SUM(
    CASE 
           WHEN job_status = 'not_active' THEN 1
           ELSE 0
        END
    ) AS total_not_active
FROM t
GROUP BY city

Check it here

CodePudding user response：

You can use a CASE statement to test whether to status is "not_active".

SELECT
  city,
  COUNT(member) AS total_members,
  SUM(CASE 
        WHEN job_status='not_active'
        THEN 1 
        ELSE 0 
      END) AS total_not_active
FROM sample
GROUP BY city
ORDER BY city;