PostgreSQL SUM Function

The SUM() function in PostgreSQL is an aggregate function that calculates the total sum of a numeric column. It's essential for financial calculations, analytics, and reporting.

Syntax

-- Basic sum
SUM(column_name)

-- Sum distinct values
SUM(DISTINCT column_name)

-- Sum with filter
SUM(column_name) FILTER (WHERE condition)

-- Sum with CASE for conditional aggregation
SUM(CASE WHEN condition THEN value ELSE 0 END)

Official Documentation

Example usage

-- Calculate total revenue
SELECT SUM(total_amount) AS total_revenue
FROM orders;

-- Sum with GROUP BY
SELECT
    category,
    SUM(price * quantity) AS total_sales
FROM products
GROUP BY category;

-- Sum with multiple aggregations
SELECT
    user_id,
    SUM(amount) AS total_spent,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY user_id;

-- Conditional sums using FILTER
SELECT
    SUM(amount) AS total_revenue,
    SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue,
    SUM(amount) FILTER (WHERE status = 'refunded') AS refunded_amount
FROM transactions;

-- Monthly revenue calculation
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS monthly_revenue
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY month
ORDER BY month;

-- Sum with DISTINCT (sum unique values only)
SELECT SUM(DISTINCT amount) AS unique_amounts_total
FROM payments;

-- Conditional sum using CASE
SELECT
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_total,
    SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_total
FROM orders;

-- Running total (window function)
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Sum with HAVING clause
SELECT
    customer_id,
    SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 1000;

Common issues

1. NULL values: SUM ignores NULL values. If all values are NULL, SUM returns NULL (not 0).
2. Data type overflow: Summing large values may cause overflow. Use appropriate numeric types.
3. Empty result sets: SUM returns NULL for empty result sets, not 0.
4. Integer division: Be careful with integer types; use NUMERIC or DECIMAL for precision.

Best practices

1. Use COALESCE to convert NULL results to 0: COALESCE(SUM(amount), 0).
2. For monetary calculations, use NUMERIC or DECIMAL types to avoid floating-point precision issues.
3. Use FILTER clause for cleaner conditional aggregation instead of CASE WHEN.
4. Consider using window functions for running totals instead of subqueries.

Frequently Asked Questions

Q: What does SUM return when all values are NULL?
A: SUM returns NULL if all input values are NULL or if the result set is empty. Use COALESCE(SUM(column), 0) if you need 0 instead of NULL.

Q: How do I calculate a running total?
A: Use a window function: SUM(amount) OVER (ORDER BY date). This calculates cumulative sum without grouping rows.

Q: What's the difference between SUM and SUM(DISTINCT)?
A: SUM adds all values including duplicates. SUM(DISTINCT) adds each unique value only once, ignoring duplicates.

Q: Can SUM cause integer overflow?
A: Yes, when summing INTEGER columns, the result might overflow. PostgreSQL automatically promotes to BIGINT, but for very large sums, use NUMERIC: SUM(column::NUMERIC).

Q: How do I sum values from multiple conditions in one query?
A: Use FILTER clause: SUM(amount) FILTER (WHERE status = 'paid') or CASE: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END).