Skip to main content
Skip to main content
Edit this page

avgIf

Description​

The If combinator can be applied to the avg function to calculate the arithmetic mean of values for rows where the condition is true, using the avgIf aggregate combinator function.

Example usage​

In this example, we'll create a table that stores sales data with success flags, and we'll use avgIf to calculate the average sale amount for successful transactions.

CREATE TABLE sales(
    transaction_id UInt32,
    amount Decimal(10,2),
    is_successful UInt8
) ENGINE = Log;

INSERT INTO sales VALUES
    (1, 100.50, 1),
    (2, 200.75, 1),
    (3, 150.25, 0),
    (4, 300.00, 1),
    (5, 250.50, 0),
    (6, 175.25, 1);

SELECT
    avgIf(amount, is_successful = 1) AS avg_successful_sale
FROM sales;

The avgIf function will calculate the average amount only for rows where is_successful = 1. In this case, it will average the amounts: 100.50, 200.75, 300.00, and 175.25.

   ┌─avg_successful_sale─┐
1. │              193.88 │
   └─────────────────────┘

See also​