条件子句中COUNT

Question

我在要求有一个问题：

SELECT regionn, COUNT(regionn) AS total, 
COUNT(last_date_o) AS ouvreurs, 
COUNT(last_date_o)*100/COUNT(regionn) AS ratio, 
COUNT(gender='mr'), COUNT(gender='mme') 
FROM data WHERE length(regionn)=2 
GROUP BY regionn ORDER BY regionn ASC 

gender列有2个可能的值：“先生”或“MME”。 COUNT（gender ='mr'），COUNT（gender ='mme'）我想要每个人的总数，但是相同的价值是回报。

事实上COUNT（性别= '先生'），COUNT（性别= 'MME'）是完全一样的COUNT（性别），COUNT（性别）...

我怎样才能做到这一点？

感谢您的帮助

Answer 1

COUNT是不是这个合适的聚合器，你应该使用SUM。

其实COUNT(gender='mr')，COUNT(gender='mme')是完全一样COUNT(gender)

这是因为COUNT计数任何非NULL，而SUM加起来一和零。

SELECT 
    regionn 
, COUNT(regionn) AS total 
, COUNT(last_date_o) AS ouvreurs\ 
, COUNT(last_date_o)*100/COUNT(regionn) AS ratio 
, SUM(CASE WHEN gender='mr' THEN 1 ELSE 0 END) AS mrs 
, SUM(CASE WHEN gender='mme' THEN 1 ELSE 0 END) AS mmes 
FROM data 
WHERE length(regionn)=2 
GROUP BY regionn 
ORDER BY regionn ASC 

Answer 2

尝试这种情况：

SELECT regionn, 
COUNT(regionn) AS total, 
COUNT(last_date_o) AS ouvreurs, 
COUNT(last_date_o)*100/COUNT(regionn) AS ratio, 
COUNT(gender='mr' or null), 
COUNT(gender='mme' or null) 
FROM data WHERE length(regionn)=2 
GROUP BY regionn ORDER BY regionn ASC 

解释为COUNT(gender='mr' or null)：

对于gender == mr

count(gender='mr' or null) =>count('mr'='mr' or null) =>count(true or null) =>count(true) =>1

对于gender == mme

count(gender='mr' or null) =>count('mme'='mr' or null) =>count(false or null) =>count(null) =>0

这招使用的事实，true or null == true和false or null == null