Aggregate Functions

Aggregate functions work in the normal way as expected by database experts.

ClickHouse also supports:

NULL Processing

During aggregation, all NULLs are skipped.

Examples:

Consider this table:

  1. ┌─x─┬────y─┐
  2. │ 1 │    2 │
  3. │ 2 │ ᴺᵁᴸᴸ │
  4. │ 3 │    2 │
  5. │ 3 │    3 │
  6. │ 3 │ ᴺᵁᴸᴸ │
  7. └───┴──────┘

Let’s say you need to total the values in the y column:

  1. SELECT sum(y) FROM t_null_big
  1. ┌─sum(y)─┐
  2. │      7 │
  3. └────────┘

The sum function interprets NULL as 0. In particular, this means that if the function receives input of a selection where all the values are NULL, then the result will be 0, not NULL.

Now you can use the groupArray function to create an array from the y column:

  1. SELECT groupArray(y) FROM t_null_big
  1. ┌─groupArray(y)─┐
  2. │ [2,2,3]       │
  3. └───────────────┘

groupArray does not include NULL in the resulting array.

Original article