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
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.