PostgreSQL - SUM Function

PostgreSQL - The SUM() Function

The PostgreSQL SUM() function is an aggregate function that calculates the sum of a set of values. It is commonly used to find the total of a numeric column, often in combination with grouping to calculate subtotals.

Syntax

The basic syntax for the SUM() function is:

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

When used with the GROUP BY clause, the syntax is:

SELECT group_column, SUM(aggregate_column)
FROM table_name
GROUP BY group_column;

Setup: Sample Data

We will use the same employees table from previous lessons. If you don’t have it, you can create it with the following script:

-- Re-using the employees table for consistency
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('James', 'Smith', 'Sales', 55000.00, '2021-10-01'),
('Maria', 'Garcia', 'HR', 60000.00, '2020-05-15'),
('David', 'Smith', 'Engineering', 80000.00, '2022-01-20'),
('John', 'Johnson', 'Engineering', 95000.00, '2019-03-12'),
('Patricia', 'Williams', 'Sales', 58000.00, '2021-10-01'),
('Robert', 'Jones', 'Engineering', 80000.00, '2022-02-11'),
('Jennifer', 'Brown', 'HR', NULL, '2023-01-09');

Example 1: Calculating a Total Sum

To calculate the total salary budget for the entire company, you can use SUM() on the salary column.

SELECT SUM(salary) AS total_salary_budget
FROM employees;

Note the use of an alias (AS total_salary_budget) to give the result column a descriptive name. The query returns:

 total_salary_budget
---------------------
           428000.00
(1 row)

Handling NULLs

The SUM() function ignores NULL values. In our example, Jennifer Brown’s NULL salary was not included in the calculation. If the input set to SUM() is empty (e.g., after a WHERE clause filters out all rows), it returns NULL, not zero.

Example 2: Sum with GROUP BY

A more powerful use of SUM() is to calculate subtotals for different groups. Let’s find the total salary budget for each department.

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

This query first groups rows by their department and then applies the SUM() function to each group:

 department  | department_salary_budget
-------------+--------------------------
 Engineering |                255000.00
 HR          |                 60000.00
 Sales       |                113000.00
(3 rows)

Example 3: Sum with Filtering

You can combine SUM() with a WHERE clause to calculate the sum for a subset of data. For example, to find the total salary of employees in the ‘Engineering’ department who earn more than 80,000:

SELECT SUM(salary) AS high_earner_eng_salary
FROM employees
WHERE department = 'Engineering' AND salary > 80000;

Result:

 high_earner_eng_salary
------------------------
               95000.00
(1 row)

Practical Application: Financial Reporting

SUM() is fundamental in business intelligence and financial reporting. For example, in a sales table, you could use it to calculate:

• Total revenue for the last quarter: SELECT SUM(amount) FROM sales WHERE sale_date >= 'YYYY-MM-DD';
• Total sales per product category: SELECT category, SUM(revenue) FROM sales GROUP BY category;
• Total sales for regions with over $1M in revenue: SELECT region, SUM(revenue) FROM sales GROUP BY region HAVING SUM(revenue) > 1000000;

postgresql_useful_functions.htm