COUNT个条件子句 [英] Conditional clause in COUNT

问题描述

我的请求有问题：

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

列有两个可能的值： mr或 mme。
我有COUNT（gender ='mr'）和COUNT（gender ='mme'）的总数，但是返回的值是相同的。

the gender column has 2 possible values: 'mr' or 'mme'. With COUNT(gender='mr'), COUNT(gender='mme') I want the total of each, but the same value is return.

实际上，COUNT（gender ='mr'），COUNT（gender ='mme'）与COUNT（gender），COUNT（gender）...

In fact COUNT(gender='mr'), COUNT(gender='mme') is exactly the same as COUNT(gender), COUNT(gender)...

我该怎么做？

感谢帮助

推荐答案

COUNT 不是正确的聚合器，您应该使用 SUM 。

COUNT is not the right aggregator for this, you should use SUM.

实际上 COUNT（gender ='mr'）， COUNT（gender ='mme' ）与 COUNT（gender）

完全相同

这是因为 COUNT 计算的不是 NULL ，而 SUM 将加1和0。

This is because COUNT counts anything that's not NULL, while SUM will add up ones and zeros.

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

这篇关于COUNT个条件子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！