Three aggregate functions, only one of them with conditions

Community
,
1

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:

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:

max_value, avg_value, min_value
100   50   10

but when i run query it returns:

max_value
100
50
10
2

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.

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.