avg_if ​

Average non-NULL values from rows whose condition is SQL true.

Syntax

avg_if(value, condition)

Arguments

value: numeric expression. condition: SQL truth expression. False and NULL conditions skip numeric accumulation; selected NULL values do not enter the divisor.

Returned value

The same REAL or native decimal result as avg; NULL when no non-NULL selected value exists.

Examples

CREATE TABLE orders (amount INTEGER);
INSERT INTO orders VALUES (1),(NULL),(2);

SELECT avg_if(amount,amount > 1) FROM orders FORMAT csv;

2.0