提问者:小点点

使用聚合计数SQL


有些记录带着一些条件,我是数不清的。 例如,我有这张桌子

+-----+-----------------+-------+
| CCA |     NUMERO      | STATO |
+-----+-----------------+-------+
| 057 | 007030020004527 |     0 |
| 057 | 007030020004527 |     1 |
| 057 | 007030020004527 |     1 |
| 057 | 007030020004123 |     1 |
| 057 | 007030020004123 |     1 |
| 057 | 007030020001111 |     1 |
| 057 | 007030020001111 |     1 |
| 057 | 007030020001111 |     1 |
+-----+-----------------+-------+

我对本例的预期结果是

057 2

当count(NUMERO)=SUM(STATO)时,我想为每个CCA计数NUMERO的数目,但我不能。 我尝试了类似的操作,但结果不正确(表名为sinistro)

SELECT cca, count(numero) AS totali, sum(stato) as gestiti 
FROM `sinistro`
GROUP BY sinistro.cca
HAVING (totali - gestiti) = 0

我可以列出所有有这个条件的数字,但我不能数。 我总是得不到行。

我怎么能这么做呢?


共2个答案

匿名用户

您需要两个级别的聚合:

select t.cca, count(*) counter
from (
  select cca, numero
  from tablename
  group by cca, numero
  having count(*) = sum(stato) -- or having min(stato) = 1
) t
group by t.cca

请参阅演示。

或带有不存在:

select cca, count(distinct numero) counter
from tablename t
where not exists (
  select 1 
  from tablename
  where cca = t.cca and numero = t.numero and stato = 0
)
group by cca

请参阅演示。
结果:

| cca | counter |
| --- | ------- |
| 57  | 2       |

匿名用户

你似乎想:

select cca
from sinistro
group by cca
having count(*) = sum(stato);