ClickHouse DB::Exception: Not an aggregate function

The "DB::Exception: Not an aggregate function" error in ClickHouse occurs when your query uses a GROUP BY clause but references a column in the SELECT, HAVING, or ORDER BY that is neither aggregated nor included in the GROUP BY list. ClickHouse enforces strict grouping rules -- every selected expression must either be an aggregate function or appear in the GROUP BY. The error code is NOT_AN_AGGREGATE.

Impact

The query fails during analysis. This is a common error for developers writing ad-hoc queries or transitioning from databases like MySQL that have more lenient GROUP BY behavior (with ONLY_FULL_GROUP_BY disabled).

Common Causes

1. Selecting a column not in GROUP BY -- the most common case, where you select a column that is not part of the grouping key and not wrapped in an aggregate.
2. ORDER BY a non-grouped, non-aggregated column -- sorting by a column that is neither in GROUP BY nor aggregated.
3. HAVING on a non-aggregated expression -- filtering groups using a column that is not aggregated.
4. Accidentally omitting a column from GROUP BY -- forgetting to include all non-aggregated select columns in the GROUP BY clause.
5. Using expressions in SELECT that differ from GROUP BY -- writing GROUP BY date but selecting toDate(timestamp) without matching the expression.

Troubleshooting and Resolution Steps

1. Add the missing column to GROUP BY:

   -- Wrong: name is not aggregated or grouped
   SELECT name, department, count() FROM employees GROUP BY department;
   
   -- Correct: include name in GROUP BY
   SELECT name, department, count() FROM employees GROUP BY name, department;
   

2. Wrap the column in an aggregate function. If you do not want to group by the column, aggregate it:

   -- Use any() to pick an arbitrary value
   SELECT any(name), department, count() FROM employees GROUP BY department;
   
   -- Or use min/max for a deterministic choice
   SELECT min(name), department, count() FROM employees GROUP BY department;
   

3. Match SELECT expressions with GROUP BY expressions:

   -- Wrong: expressions don't match
   SELECT toDate(timestamp), count()
   FROM events
   GROUP BY timestamp;
   
   -- Correct: use the same expression in both
   SELECT toDate(timestamp), count()
   FROM events
   GROUP BY toDate(timestamp);
   

4. Use aliases consistently:

   -- ClickHouse allows grouping by alias
   SELECT toDate(timestamp) AS event_date, count()
   FROM events
   GROUP BY event_date;
   

5. Fix ORDER BY clauses:

   -- Wrong: ordering by non-grouped column
   SELECT department, count() AS cnt FROM employees
   GROUP BY department ORDER BY name;
   
   -- Correct: order by grouped or aggregated column
   SELECT department, count() AS cnt FROM employees
   GROUP BY department ORDER BY cnt DESC;
   

6. Use window functions if you need both detail and aggregation:

   -- Get per-row detail alongside an aggregate
   SELECT name, department,
          count() OVER (PARTITION BY department) AS dept_count
   FROM employees;
   

Best Practices

• Always verify that every non-aggregated column in SELECT appears in GROUP BY. This is a fundamental SQL rule that ClickHouse enforces strictly.
• Use any() deliberately when you want a representative value from a group but do not want to add the column to GROUP BY. Be aware that any() returns an arbitrary value from the group.
• Use column aliases to keep GROUP BY expressions in sync with SELECT expressions.
• Prefer window functions when you need both row-level detail and group-level aggregates in the same result.
• When writing complex queries, build the GROUP BY clause first and then add SELECT columns.

Frequently Asked Questions

Q: MySQL lets me select non-grouped columns without error. Why doesn't ClickHouse?
A: MySQL has a ONLY_FULL_GROUP_BY mode that is sometimes disabled, allowing ambiguous queries. ClickHouse always enforces strict grouping rules because returning arbitrary values without explicit intent can lead to misleading results. Use any() if you explicitly want an arbitrary value.

Q: Can I use the any() function to bypass this error?
A: Yes. any(column) picks an arbitrary value from the group and satisfies the aggregation requirement. This is the correct approach when you know all values in the group are the same or you do not care which one is returned.

Q: Does this error apply to HAVING clauses too?
A: Yes. Every expression in HAVING must also be either aggregated or present in GROUP BY. HAVING filters groups, not rows, so non-aggregated column references do not make sense.

Q: I am using GROUP BY with TOTALS or ROLLUP and getting this error. What should I check?
A: The same rules apply with GROUP BY modifiers like WITH TOTALS, WITH ROLLUP, and WITH CUBE. All non-aggregated columns in SELECT must still appear in GROUP BY. The modifiers add extra summary rows but do not change the grouping validation rules.