ClickHouse DB::Exception: Unknown aggregate function

The "DB::Exception: Unknown aggregate function" error in ClickHouse appears when you reference an aggregate function that the server does not recognize. This is separate from the general "Unknown function" error -- ClickHouse distinguishes between regular (scalar) functions and aggregate functions internally, and this specific error code (UNKNOWN_AGGREGATE_FUNCTION) applies only to the aggregate function namespace.

Impact

The query fails during analysis, before any data is read. This prevents any aggregation results from being returned. If the unrecognized function is used inside a materialized view definition, it will block INSERTs into the source table.

Common Causes

1. Misspelled aggregate function name -- for example, writing cunt() instead of count() or sumIf instead of sumIf with correct casing.
2. Using a function that does not exist in your ClickHouse version -- newer aggregate functions like quantileExactWeighted or sparkBar may not be available in older releases.
3. Confusing scalar and aggregate functions -- trying to use a scalar function in an aggregate context or vice versa.
4. Missing combinator suffix -- aggregate function combinators like -If, -Array, -State, -Merge must be appended correctly (e.g., sumIf, not sum_if).
5. Attempting to use a UDF as an aggregate -- user-defined functions created with CREATE FUNCTION are scalar only; they cannot be used as aggregate functions.

Troubleshooting and Resolution Steps

1. Check the exact function name and spelling. Aggregate functions in ClickHouse are case-insensitive, but combinators must be appended directly without separators:

   -- Correct combinator usage
   SELECT sumIf(amount, status = 'paid') FROM orders;
   
   -- Wrong: underscore separator
   SELECT sum_If(amount, status = 'paid') FROM orders;
   

2. List available aggregate functions. Query the system table to check if your function exists:

   SELECT name FROM system.functions WHERE is_aggregate = 1 ORDER BY name;
   

3. Verify your ClickHouse version supports the function:

   SELECT version();
   

   Then check the documentation for when the function was introduced.

4. Check combinator compatibility. Not all combinators work with all aggregate functions. The standard combinators are -If, -Array, -State, -Merge, -ForEach, -Distinct, -OrDefault, -OrNull, -Resample:

   -- Valid: countIf with -If combinator
   SELECT countIf(x > 10) FROM table;
   
   -- Valid: chaining combinators
   SELECT sumIfState(amount, status = 'paid') FROM orders;
   

5. Distinguish between parametric and regular arguments. Some aggregate functions take parameters in a special syntax:

   -- Correct parametric syntax
   SELECT quantile(0.95)(response_time) FROM requests;
   

6. Consider upgrading ClickHouse. If you need a function only available in a newer version, plan an upgrade.

Best Practices

• Use auto-complete in your SQL client to avoid typos in aggregate function names.
• Familiarize yourself with ClickHouse's aggregate function combinators -- they are powerful and can eliminate the need for complex subqueries.
• When building queries programmatically, validate function names against system.functions before execution.
• Pin your ClickHouse version in development and testing environments to match production, so you do not accidentally use functions unavailable in production.

Frequently Asked Questions

Q: What is the difference between UNKNOWN_AGGREGATE_FUNCTION and UNKNOWN_FUNCTION?
A: ClickHouse maintains separate registries for scalar functions and aggregate functions. If you use an unknown name in a non-aggregate context, you get UNKNOWN_FUNCTION. If it is used where an aggregate is expected (e.g., in a GROUP BY query), you get UNKNOWN_AGGREGATE_FUNCTION.

Q: Can I create custom aggregate functions in ClickHouse?
A: ClickHouse does not currently support user-defined aggregate functions through SQL. Custom aggregates require C++ implementation compiled into the server. The CREATE FUNCTION statement only creates scalar lambda-based UDFs.

Q: I am using the -State combinator and getting this error. Why?
A: Make sure you are appending State directly to the function name without any separator: sumState, avgState, uniqState. Also verify the base function exists -- fooState will fail if foo is not a valid aggregate function.

Q: Does ClickHouse support window functions like ROW_NUMBER as aggregate functions?
A: Window functions like row_number(), rank(), and dense_rank() are not aggregate functions. They are used with the OVER clause and exist in a separate namespace. Using them without OVER or in an aggregate context will produce errors.