# Three aggregate functions, only one of them with conditions

**URL:** <https://community.questdb.com/t/three-aggregate-functions-only-one-of-them-with-conditions/144>\
**Category:** Community\
**Tags:** sql, question\
**Created:** [August 13, 2024, 6:11pm UTC](https://community.questdb.com/t/three-aggregate-functions-only-one-of-them-with-conditions/144 "2024-08-13T18:11:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [August 13, 2024, 6:11pm UTC](https://community.questdb.com/t/three-aggregate-functions-only-one-of-them-with-conditions/144/1 "2024-08-13T18:11:45Z")

</div>

I want to run three aggregations (`max`, `avg`, `min`) in one questdb query, but the last of the aggregations should have a `where` condition that does not affect the others.

I tried a query that works but it gives me three rows instead of a row with three columns:

```sql
SELECT max(Duration) AS max_value FROM mytable
UNION ALL
SELECT avg(Duration) AS avg_value FROM mytable
UNION ALL
SELECT min(Duration) AS min_value FROM mytable WHERE code IN ('0000', '4000', '6000');

```

Expected output:

```auto
max_value, avg_value, min_value
100 50 10

```

but when i run query it returns:

```auto
max_value
100
50
10

```

---

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [August 13, 2024, 6:12pm UTC](https://community.questdb.com/t/three-aggregate-functions-only-one-of-them-with-conditions/144/2 "2024-08-13T18:12:03Z")

</div>

The `UNION` is doing as expected. You run three queries, each returning one row, you get three rows back. Moreover, since the column in the first query is aliased as `max_value`, then the three rows have the same column name, as in the `UNION` the results are assumed to have the same shape, so the position and type are important, but the names in the subsequent queries are ignored.

You could do a CTE query with the three queries and a join, but that would be probably overkill here. It is simpler to use `CASE` so the `min` column aggregates conditionally.

```sql
SELECT 
  max(Duration) AS max_value, 
  avg(Duration) AS avg_value,  
  min(
      CASE 
        WHEN code IN ('0000', '4000', '6000') 
        THEN Duration 
      END 
      ) AS min_value 
FROM mytable

```

When the query is executed, the `CASE` condition is evaluated. If it matches, then the `Duration` column value is used in the aggregate fuction, otherwise a `null` value is returned for that column. This way you can have different conditions applied to different aggregation functions in a single query.