Skip to main content

PostgreSQL SUM

The `SUM` function in PostgreSQL calculates the total of a numeric column. It is a key tool for aggregating values and deriving insights from data.

Usage

The `SUM` function is employed to find the total of a numeric column, often used with the `GROUP BY` clause for aggregated data. It is particularly useful in financial and statistical calculations.

SELECT SUM(column_name)
FROM table_name
[WHERE condition];

In this syntax, `SUM(column_name)` computes the total of the specified column from the rows filtered by the `WHERE` clause, if provided.

Data Type Compatibility

The `SUM` function is compatible with numeric data types such as `INT`, `FLOAT`, `NUMERIC`, etc. Attempting to sum a non-numeric column will result in an error.

Examples

1. Basic Sum Calculation

SELECT SUM(salary)
FROM employees;

This example calculates the total sum of all salaries from the `employees` table.

2. Sum with Condition

SELECT SUM(salary)
FROM employees
WHERE department = 'Sales';

Here, the `SUM` function calculates the total salary for employees in the 'Sales' department by applying a `WHERE` clause.

3. Using SUM with GROUP BY

SELECT department, SUM(salary)
FROM employees
GROUP BY department;

In this example, the `SUM` function is used with `GROUP BY` to calculate and return the total salary for each department.

4. Handling NULL Values with COALESCE

SELECT SUM(COALESCE(salary, 0))
FROM employees;

This example demonstrates using `COALESCE` to treat `NULL` values as zero during the sum calculation.

5. Ordering Results After Aggregation

SELECT department, SUM(salary)
FROM employees
GROUP BY department
ORDER BY SUM(salary) DESC;

This example shows how to order results based on the aggregated sum in descending order.

Tips and Best Practices

  • Filter Data Appropriately: Use `WHERE` clauses to exclude irrelevant data for a more accurate sum.
  • Combine with GROUP BY: When analyzing data across categories, use `GROUP BY` to get sums for each group.
  • Handle NULL Values: Use `COALESCE` to handle `NULL` values in numeric columns.
  • Ensure Numeric Data Types: Ensure the column you are summing is a numeric data type to avoid errors.
  • Performance Consideration: For large datasets, consider indexing columns involved in `WHERE` and `GROUP BY` clauses to optimize query performance.