Skip to content
Coordinator DB
View as Markdown
View as Markdown

avg_if ​

Calculate the average amount of paid orders. A conditional aggregate admits rows whose condition is SQL true, so one query can calculate a segment without filtering every other aggregate to that same segment.

Prepare the input ​

The sample includes two paid orders and one pending order:

sql
CREATE TABLE orders(id UInt32, status String, amount Int64);
INSERT INTO orders VALUES (1, 'paid', 100), (2, 'paid', 60), (3, 'pending', 40);

Calculate the segment ​

sql
SELECT avg_if(amount, status = 'paid') AS paid_result
FROM orders
FORMAT csv;
text
80.0

Selected NULL amounts are skipped. False and NULL conditions do not contribute.

Empty segments ​

avg_if returns NULL when no qualifying non-NULL value contributes. Arguments are evaluated before the condition is tested; an excluded row does not protect an argument expression from an evaluation error.

See aggregate combinators for the supported conditional functions and SQL FILTER alternatives.