---
title: NOT_AN_AGGREGATE ClickHouse error
meta:
  description: Learn how to fix the NOT_AN_AGGREGATE error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# NOT_AN_AGGREGATE ClickHouse error

{% callout %}
This error usually means you're trying to use a non-aggregate column in a SELECT clause without including it in GROUP BY, or you're mixing aggregate and non-aggregate columns incorrectly.
{% /callout %}

The `NOT_AN_AGGREGATE` error in ClickHouse (and Tinybird) happens when you try to use a non-aggregate column in a query that contains aggregate functions, but the column isn't included in the GROUP BY clause. This is a common SQL error that occurs when mixing aggregate and non-aggregate columns.

## What causes this error

You'll typically see it when:

* Using non-aggregate columns in SELECT with aggregate functions without GROUP BY
* Mixing aggregate and non-aggregate columns incorrectly
* Using window functions incorrectly
* Forgetting to include columns in GROUP BY clause

## Example errors

```sql {% title="Fails: non-aggregate column without GROUP BY" %}
SELECT user_id, COUNT(*) FROM events
```

```sql {% title="Fails: mixing aggregate and non-aggregate" %}
SELECT user_id, email, COUNT(*) FROM events GROUP BY user_id
```

```sql {% title="Fails: non-aggregate column with aggregate without GROUP BY" %}
SELECT user_id, COUNT(*) FROM events WHERE user_id > 0
```

```sql {% title="Fails: aggregate in HAVING without GROUP BY" %}
SELECT user_id, COUNT(*) FROM events HAVING COUNT(*) > 10
```

## How to fix it

### Add missing GROUP BY

Include all non-aggregate columns in the GROUP BY clause:

```sql {% title="Fix: add user_id to GROUP BY" %}
SELECT user_id, COUNT(*) FROM events GROUP BY user_id
```

### Use subqueries for complex aggregations

When you need to filter on aggregates, use subqueries:

```sql {% title="Fix: use subquery for aggregate filtering" %}
SELECT user_id, COUNT(*) as count
FROM events
GROUP BY user_id
HAVING COUNT(*) > 10
```

### Use window functions instead

For row-level calculations with aggregates, consider window functions:

```sql {% title="Fix: use window function" %}
SELECT user_id, COUNT(*) OVER (PARTITION BY user_id) as user_count
FROM events
```

## Common patterns and solutions

### Simple aggregation

When you want to aggregate the entire table:

```sql {% title="Correct: aggregate entire table" %}
SELECT COUNT(*) as total_events FROM events
```

### Grouped aggregation

When you want to aggregate by groups:

```sql {% title="Correct: group by user_id" %}
SELECT user_id, COUNT(*) as event_count
FROM events
GROUP BY user_id
```

### Multiple columns in GROUP BY

Include all non-aggregate columns:

```sql {% title="Correct: multiple columns in GROUP BY" %}
SELECT user_id, event_type, COUNT(*) as count
FROM events
GROUP BY user_id, event_type
```

### Using HAVING correctly

HAVING filters on aggregate results:

```sql {% title="Correct: HAVING with GROUP BY" %}
SELECT user_id, COUNT(*) as count
FROM events
GROUP BY user_id
HAVING COUNT(*) > 5
```

## Advanced solutions

### Conditional aggregation

Use CASE statements within aggregate functions:

```sql {% title="Conditional aggregation" %}
SELECT user_id,
       COUNT(*) as total_events,
       COUNT(CASE WHEN event_type = 'click' THEN 1 END) as clicks
FROM events
GROUP BY user_id
```

### Multiple aggregates

You can use multiple aggregate functions in the same query:

```sql {% title="Multiple aggregates" %}
SELECT user_id,
       COUNT(*) as total_events,
       SUM(value) as total_value,
       AVG(value) as avg_value
FROM events
GROUP BY user_id
```

## Tinybird-specific notes

In Tinybird Pipes, this error often occurs when:

* Building Pipes that mix aggregate and non-aggregate operations
* Using Materialized Views incorrectly
* Forgetting to group by all non-aggregate columns in Pipe nodes

To debug in Tinybird:

1. Check each Pipe node for proper GROUP BY clauses
2. Verify that Materialized Views have correct aggregation logic
3. Use the Pipe validation to catch these errors early

## See also

* [ClickHouse Aggregate Functions](/sql-reference/functions/aggregate-functions)
* [Working with Pipes](/forward/core-concepts/pipes)
* [Common function errors](/sql-reference/clickhouse-errors/ILLEGAL_TYPE_OF_ARGUMENT)