avgIf

Example of using the avgIf combinator

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.

Query

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

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.

Response

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

See also

• avg
• If combinator