Introduction to PostgreSQL SUM() function

The PostgreSQLSUM() is an aggregate function that returns the sum of values in a set.

Here’s the basic syntax of theSUM() function:

SUM(DISTINCT expression)

TheSUM() function ignoresNULL, meaning that it doesn’t consider theNULL in calculation.

If you use theDISTINCT option, theSUM() function calculates the sum of only distinct values.

For example, without theDISTINCT option, theSUM() of 1, 1, and 2 will return 4. But the sum of distinct values of 1, 1, and 2 will return 3 (1 + 2) because theSUM() function ignores one duplicate value (1).

TheSUM() of an empty set will returnNULL, not zero.

PostgreSQL SUM() function examples

Let’s take some examples of using theSUM() function. We’ll use thepayment table in thesample database.

1) Using PostgreSQL SUM() function in SELECT statement example

The following example uses theSUM() function to calculate the sum of all payments in thepayment table:

SELECT  SUM(amount)FROM  payment;

Output:

sum---------- 61312.04(1 row)

2) Using PostgreSQL SUM() function with an empty result set

The following statement uses theSUM() function to calculate the total payment of the customer id 2000.

SELECT  SUM (amount)FROM  paymentWHERE  customer_id= 2000;

Output:

sum------ null(1 row)

In this example, theSUM() function returns aNULL because thepayment the table has no row with thecustomer_id 2000.

3) Using the SUM() function with COALESCE() function example

If you want theSUM() function to return zero instead ofNULL in case there is no matching row, you use theCOALESCE() function.

TheCOALESCE() function returns the first non-null argument. In other words, it returns the second argument if the first argument isNULL.

The following query illustrates how to use theSUM() function with theCOALESCE() function:

SELECT  COALESCE(SUM(amount),0 ) totalFROM  paymentWHERE  customer_id= 2000;

Output:

total-------     0(1 row)

4) Using PostgreSQL SUM() function with the GROUP BY clause example

To calculate the summary of every group, you use theGROUP BY clause to group the rows in the table into groups and apply theSUM() function to each group.

The following example uses theSUM() function with theGROUP BY clause to calculate the total amount paid by each customer:

SELECT  customer_id,  SUM (amount)AS totalFROM  paymentGROUP BY  customer_idORDER BY  total;

Output:

customer_id | total-------------+--------         318 |  27.93         281 |  32.90         248 |  37.87         320 |  47.85...

The following query retrieves the top five customers who made the highest payments:

SELECT  customer_id,  SUM (amount) AS totalFROM  paymentGROUP BY  customer_idORDER BY  total DESCLIMIT  5;

Output:

customer_id | total-------------+--------         148 | 211.55         526 | 208.58         178 | 194.61         137 | 191.62         144 | 189.60(5 rows)

5) Using PostgreSQL SUM() function with a HAVING clause

To filter group sums based on a specific condition, you use theSUM() function in theHAVING clause.

The following example retrieves customers who have made payments exceeding 200:

SELECT  customer_id,  SUM (amount) AS totalFROM  paymentGROUP BY  customer_idHAVING  SUM(amount)> 200ORDER BY  total DESC

Output:

customer_id | total-------------+--------         148 | 211.55         526 | 208.58(2 rows)

6) Using PostgreSQL SUM() function with an expression

See the followingrental table from thesample database:

The following statement uses theSUM() function to calculate the total rental days:

SELECT  SUM(return_date - rental_date)FROM  rental;

Output:

sum------------------------- 71786 days 190098:21:00(1 row)

How it works.

• First, calculate the rental duration by subtracting the rental date from the return date.
• Second, apply theSUM() function to the expression.

The following example uses theSUM() function to calculate the total duration by customers:

SELECT  first_name || ' ' || last_name full_name,  SUM(return_date - rental_date) rental_durationFROM  rental  INNER JOIN customer USING(customer_id)GROUP BY  customer_idORDER BY  full_name;

Output:

full_name       |  rental_duration-----------------------+-------------------- Aaron Selby           | 109 days 273:34:00 Adam Gooch            | 106 days 245:18:00 Adrian Clary          | 90 days 286:00:00 Agnes Bishop          | 97 days 339:40:00...

Summary

• Use theSUM() function to calculate the sum of values in a set.
• Use theDISTINCT option in theSUM() function to calculate the sum of distinct values.
• Use theSUM() function with theGROUP BY clause to calculate the sum for each group.

Last updated onJanuary 26, 2024

On this page

• Introduction to PostgreSQL SUM() function
• PostgreSQL SUM() function examples
• Summary