Summing Data with SUM() — Syntax and Examples

Imagine this... You’ve got a super long shopping list from the supermarket. You want to figure out how much money you spent in total. You’re not gonna add it all up by hand, right? But the store clerks already have everything in their database. That’s why you get a receipt with the correct total. Most likely, somewhere in their app, the hero of today’s lecture is helping them out — the SUM() function! It’s an aggregate function that lets you add up values in a numeric column.

Syntax of SUM()

The SUM() function looks just as simple as its name, but let’s break it down:

SELECT SUM(column)
FROM table;

The function takes all the values in the specified column and adds them up. It works with numeric types (SMALLINT, INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION) as well as the INTERVAL and MONEY types. For strings or regular dates — it doesn't work.

Examples of Using SUM()

Let’s start with a simple example. We’ll work with a salaries table that stores employee salaries:

Example 1: Summing All Salaries

You want to know the company’s total payroll. Here’s how you do it:

SELECT SUM(salary) AS total_salary
FROM salaries;

Result:

What happened here? PostgreSQL added up all the values from the salary column (50000 + 60000 + 55000 + 75000) and returned the result in a new column called total_salary.

Example 2: Summing with a Condition

Let’s say you want to know the total salary only for employees who make more than 55,000. Time to use our favorite WHERE operator:

SELECT SUM(salary) AS high_salary_total
FROM salaries
WHERE salary > 55000;

Result:

What happened here? First, PostgreSQL applied the filter WHERE salary > 55000, leaving only the rows with salaries 60000 and 75000. Then it added those two salaries (60000 + 75000).

3. Features of How SUM() Works

How does NULL affect SUM()? Like we’ve seen, NULL means "nothing", and SUM() doesn’t include it in calculations. Check out this table:

If we want to know the total payroll:

SELECT SUM(salary) AS total_salary
FROM salaries;

Result:

Why "185000" and not "NULL"? PostgreSQL just ignores NULL when calculating the sum.

Examples of Complex Queries with SUM()

Example 1: Summing and Filtering in One Query

Imagine a sales table that stores sales data. Here’s its structure:

You want to know the total sales amount just for the first product product_id = 1:

SELECT SUM(amount) AS total_sales
FROM sales
WHERE product_id = 1;

Result:

Example 2: Sum and Extra Calculations

Back to the salaries table. You want to know how much the payroll exceeds 200,000:

SELECT SUM(salary) - 200000 AS surplus
FROM salaries;

Result:

Common Mistakes When Using SUM()

Using SUM() on Non-Numeric Data: If you accidentally try to sum, say, text values, you’ll get an error. Be careful and check the data types in your column.

Ignoring NULL: Beginners often forget about NULL and think it’s included in calculations. That can lead to unexpected results.