我有一個樣本表
city member job_status
NY John not_active
NY Sam not_active
NY Phil active
WDC Martha active
WDC Harvey not_active
我怎樣才能得到一個匯總表，如
city total_members total_not_active
NY 3 2
WDC 2 1
select city, count(member) total_members from sample group by city
我可以使用上面的那個，但是如何獲得 total_not_acitve 呢？
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
用這個
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
uj5u.com熱心網友回復：
您可以使用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
在這里檢查
uj5u.com熱心網友回復：
您可以使用CASE陳述句來測驗狀態是否為“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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/448893.html
上一篇：Laravel復制記錄并使用新值復制但不使用唯一欄位
下一篇：如何總結不同列的條件