用聚合计数SQL

Marco Bozzola:

在某些情况下,我无法计算某些记录。我有这个桌子

+-----+-----------------+-------+
| 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

我想为每个CCA计算COUNT(NUMERO)= SUM(STATO)时的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

参见演示

或搭配NOT EXISTS

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       |

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章