Search code examples
clickhouse

Clickhouse: 0 for absent aggregate

Imagine there is a table:

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

I would like to count (aggregate) by code and for absent codes counted value to 0:

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

The actual result for query above:

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

But I'd expect result to be:

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

Is it possible to archive expected result? If yes how?

Solution

  • You can do something like this:

    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`

    Follow this SQL fiddel.