# Postgres sum() function

Calculate the sum of a set of values

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:

`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.

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

## Advanced examples

### Sum with grouping

You can use `sum()`

with `GROUP BY`

to calculate subtotals for different categories:

This query calculates the total sales for each department.

### Sum with FILTER clause

You can use the `FILTER`

clause to conditionally include values in the sum:

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

### Sum over a window

You can use `sum()`

as a window function to calculate running totals:

This query calculates a running total of sales over time.

## 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

Last updated on