PostgreSQL AVG Function

The AVG() function in PostgreSQL calculates the arithmetic mean (average) of a set of values. It's widely used for statistical analysis, reporting, and data summarization.

Syntax

-- Basic average
AVG(column_name)

-- Average of distinct values
AVG(DISTINCT column_name)

-- Average with filter
AVG(column_name) FILTER (WHERE condition)

Official Documentation

Example usage

-- Calculate average price
SELECT AVG(price) AS average_price
FROM products;

-- Average with GROUP BY
SELECT
    category,
    AVG(price) AS avg_price,
    COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Round average to 2 decimal places
SELECT
    category,
    ROUND(AVG(price)::NUMERIC, 2) AS avg_price
FROM products
GROUP BY category;

-- Multiple averages with conditions
SELECT
    AVG(rating) AS overall_avg,
    AVG(rating) FILTER (WHERE category = 'Electronics') AS electronics_avg,
    AVG(rating) FILTER (WHERE category = 'Books') AS books_avg
FROM products;

-- Average with DISTINCT (average of unique values)
SELECT AVG(DISTINCT salary) AS avg_unique_salary
FROM employees;

-- Average order value by customer
SELECT
    customer_id,
    COUNT(*) AS order_count,
    AVG(total_amount) AS avg_order_value,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING AVG(total_amount) > 50;

-- Moving average (window function)
SELECT
    date,
    temperature,
    AVG(temperature) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM weather_data;

-- Average response time by endpoint
SELECT
    endpoint,
    AVG(response_time_ms) AS avg_response_ms,
    MIN(response_time_ms) AS min_response_ms,
    MAX(response_time_ms) AS max_response_ms
FROM api_logs
WHERE timestamp > NOW() - INTERVAL '24 hours'
GROUP BY endpoint
ORDER BY avg_response_ms DESC;

Common issues

1. NULL values: AVG ignores NULL values in calculation. If all values are NULL, returns NULL.
2. Integer division: AVG on INTEGER columns returns INTEGER in older versions; cast to NUMERIC for decimals.
3. Precision: Result precision depends on input data type. Use explicit casting for control.
4. Empty result set: Returns NULL for empty sets, not 0.

Best practices

1. Cast to NUMERIC or DECIMAL for precise decimal results: AVG(column::NUMERIC).
2. Use ROUND() to control decimal places in output.
3. Use FILTER clause for conditional averages instead of subqueries.
4. Consider using window functions for moving averages and running calculations.

Frequently Asked Questions

Q: How does AVG handle NULL values?
A: AVG ignores NULL values completely. If you have 5 values where 2 are NULL, it averages the 3 non-NULL values. If all values are NULL, AVG returns NULL.

Q: Why is my average returning an integer instead of a decimal?
A: In some PostgreSQL versions, AVG on INTEGER columns may truncate. Use AVG(column::NUMERIC) or AVG(column)::NUMERIC to get decimal results.

Q: What's the difference between AVG and AVG(DISTINCT)?
A: AVG calculates the mean of all values including duplicates. AVG(DISTINCT) calculates the mean of unique values only, treating each distinct value once.

Q: How do I calculate a weighted average?
A: Use SUM and division: SUM(value * weight) / SUM(weight). For example: SUM(price * quantity) / SUM(quantity) for average price weighted by quantity.

Q: Can I calculate average excluding outliers?
A: Yes, use FILTER or WHERE to exclude outliers: AVG(value) FILTER (WHERE value BETWEEN 10 AND 100) or use percentile functions to identify outliers first.