Postgres sum() function

The Postgres sum() function calculates the total of a set of numeric values.

It's used in data analysis and reporting to compute totals across rows in a table or grouped data. This function is particularly useful in financial applications for calculating total revenue or expenses, in inventory management for summing up quantities, or in analytics for aggregating metrics across various dimensions.

Function signature

The sum() function has this simple form:

sum([DISTINCT] expression) -> numeric type

• expression: Any numeric expression or column name. The function returns a value of the same data type as the input.
• DISTINCT: Optional keyword that causes sum() to consider only unique values in the calculation.

The output of the sum() function has the same data type as the input if it's a floating-point (real / double-precision) type. To avoid overflow, the output for smallint/integer inputs is a bigint, and for bigint/numeric inputs, it is numeric type.

Example usage

Consider a sales table that tracks product sales, with columns product_id, quantity, and price. We can use sum() to calculate the total revenue from each product.

WITH sales(product_id, quantity, price) AS (
  VALUES
    (1, 10, 100.0),
    (2, 5, 50.0),
    (1, 5, 100.0),
    (3, 3, 75.0),
    (2, 2, 50.0)
)
SELECT sum(quantity * price) AS total_revenue
FROM sales;

This query calculates the total revenue by multiplying the quantity and price for each sale.

total_revenue
---------------
        2075.0
(1 row)

Advanced examples

Sum with grouping

You can use sum() with GROUP BY to calculate subtotals for different categories:

WITH employee_sales AS (
  SELECT 'Alice' AS employee, 'Electronics' AS department, 5000 AS sales
  UNION ALL
  SELECT 'Bob' AS employee, 'Electronics' AS department, 6000 AS sales
  UNION ALL
  SELECT 'Charlie' AS employee, 'Clothing' AS department, 4500 AS sales
  UNION ALL
  SELECT 'David' AS employee, 'Clothing' AS department, 5500 AS sales
)
SELECT department, sum(sales) AS total_sales
FROM employee_sales
GROUP BY department;

This query calculates the total sales for each department.

department  | total_sales
-------------+-------------
 Clothing    |       10000
 Electronics |       11000
(2 rows)

Sum with FILTER clause

You can use the FILTER clause to conditionally include values in the sum:

WITH orders AS (
  SELECT 1 AS order_id, 'completed' AS status, 100 AS total
  UNION ALL
  SELECT 2 AS order_id, 'pending' AS status, 150 AS total
  UNION ALL
  SELECT 3 AS order_id, 'completed' AS status, 200 AS total
  UNION ALL
  SELECT 4 AS order_id, 'cancelled' AS status, 75 AS total
)
SELECT
  sum(total) AS all_orders_total,
  sum(total) FILTER (WHERE status = 'completed') AS completed_orders_total
FROM orders;

This query calculates the sum of all order totals and the sum of only completed order totals.

all_orders_total | completed_orders_total
------------------+------------------------
              525 |                    300
(1 row)

Sum over a window

You can use sum() as a window function to calculate running totals:

WITH monthly_sales AS (
  SELECT
    '2023-01-01'::date AS month,
    10000 AS sales
  UNION ALL
  SELECT '2023-02-01'::date, 12000
  UNION ALL
  SELECT '2023-03-01'::date, 15000
  UNION ALL
  SELECT '2023-04-01'::date, 11000
)
SELECT
  month,
  sales,
  sum(sales) OVER (ORDER BY month) AS running_total
FROM monthly_sales;

This query calculates a running total of sales over time.

month    | sales | running_total
------------+-------+---------------
 2023-01-01 | 10000 |         10000
 2023-02-01 | 12000 |         22000
 2023-03-01 | 15000 |         37000
 2023-04-01 | 11000 |         48000
(4 rows)

Additional considerations

Null values

The sum() function ignores NULL values in its calculations. If all values are NULL, sum() returns NULL. Additionally, if there are no rows to sum over, sum() returns NULL instead of 0 which might be unexpected.

Overflow handling

When summing very large numbers, be aware of potential overflow issues. Consider using larger data types (e.g., bigint instead of integer) or the numeric type for precise calculations with large numbers.

Alternative functions

• avg(): Calculates the average of a set of values.
• count(): Counts the number of rows or non-null values.
• max() and min(): Find the maximum and minimum in a set of values.

Resources

• PostgreSQL documentation: Aggregate Functions
• PostgreSQL documentation: Window Functions