Clickhouse：0用于缺席汇总

Question

想象一下有一个表：

code | value
------------
AAAA | 10
AAAA | 15
BBBB | 5

我想按代码计数（汇总），而缺乏代码计数为0：

select code, count(*) count
from table
where code in ('AAAA', 'CCCC')
group by code

上述查询的实际结果：

code | count
------------
AAAA | 2

但是我希望结果是：

code | count
------------
AAAA | 2
CCCC | 0

是否可以归档预期结果？如果是的话？

Answer 1

您可以做这样的事情：

SELECT `F`.`code`, IFNULL(`AT`.`count`, 0) as `count` FROM 
( 
  SELECT `code`, count(*) as `count` FROM `table` 
  WHERE `code` IN ('AAAA', 'CCCC') group by `code`
) as `AT`
RIGHT JOIN 
(
  SELECT 'AAAA' as `code`
  UNION 
  SELECT 'CCCC' as `code`
) as `F`
ON `F`.`code` = `AT`.`code`

请按照此操作 sql fiddel 。