DocsAggregationsSUM & AVG

SUM & AVG

Aggregations

Concept

SUM adds up all non-NULL values in a numeric column, while AVG computes the arithmetic mean. These two aggregates are the workhorses of financial and analytical reporting — revenue totals, average order values, cost summaries, and more.

Both functions ignore NULL values. This means AVG divides by the number of non-NULL values, not the total row count. If you need NULLs to count as zero in your average, wrap the column in COALESCE(column, 0) before aggregating. SUM of an all-NULL group returns NULL, not zero — use COALESCE(SUM(column), 0) to guard against this.

SUM and AVG can operate on expressions, not just raw columns. You can compute SUM(unit_price * quantity) directly in the aggregate call. Combined with GROUP BY, they let you build rich summary reports at any level of granularity — per customer, per month, per product category, and so on.

Syntax

-- Total and average for the whole table
SELECT
  SUM(total_amount)  AS revenue,
  AVG(total_amount)  AS avg_order_value
FROM orders;

-- Per-group aggregation
SELECT
  category,
  SUM(price) AS total_value,
  AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Aggregating an expression
SELECT SUM(unit_price * quantity) AS gross_revenue
FROM order_items;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Revenue and average order value by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total_amount)               AS monthly_revenue,
  AVG(total_amount)               AS avg_order_value,
  COUNT(*)                        AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Average spend per customer (only customers with orders)
SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  SUM(o.total_amount)  AS lifetime_spend,
  AVG(o.total_amount)  AS avg_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY lifetime_spend DESC
LIMIT 10;

Common Pitfalls & Tips

1SUM of an all-NULL group returns NULL, not 0. Use COALESCE(SUM(col), 0) when you need a guaranteed numeric result.
2AVG ignores NULLs, which can skew results. If NULL means 'zero' in your domain, wrap with COALESCE before averaging.
3Watch for integer division. In some databases AVG of an integer column truncates to integer. Cast to NUMERIC or DECIMAL for accurate averages.

Practice SUM & AVG queries