PostgreSQL SUM Function (original) (raw)

Summary: in this tutorial, you’ll learn how to use PostgreSQL SUM() function to calculate the sum of a set of values.

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

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

SUM(DISTINCT expression)

The SUM() function ignores NULL, meaning that it doesn’t consider the NULL in calculation.

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

For example, without the DISTINCT option, the SUM() 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 the SUM() function ignores one duplicate value (1).

The SUM() of an empty set will return NULL, not zero.

Let’s take some examples of using the SUM() function. We’ll use the payment table in the sample database.

payment table

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

SELECT
  SUM(amount)
FROM
  payment;

Output:

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

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

SELECT
  SUM (amount)
FROM
  payment
WHERE
  customer_id = 2000;

Output:

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

In this example, the SUM() function returns a NULL because the payment the table has no row with the customer_id 2000.

If you want the SUM() function to return zero instead of NULL in case there is no matching row, you use the COALESCE() function.

The COALESCE() function returns the first non-null argument. In other words, it returns the second argument if the first argument is NULL.

The following query illustrates how to use the SUM() function with the COALESCE() function:

SELECT
  COALESCE(SUM(amount), 0 ) total
FROM
  payment
WHERE
  customer_id = 2000;

Output:

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

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

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

SELECT
  customer_id,
  SUM (amount) AS total
FROM
  payment
GROUP BY
  customer_id
ORDER 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 total
FROM
  payment
GROUP BY
  customer_id
ORDER BY
  total DESC
LIMIT
  5;

Output:

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

To filter group sums based on a specific condition, you use the SUM() function in the HAVING clause.

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

SELECT
  customer_id,
  SUM (amount) AS total
FROM
  payment
GROUP BY
  customer_id
HAVING
  SUM(amount) > 200
ORDER BY
  total DESC

Output:

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

See the following rental table from the sample database:

The following statement uses the SUM() 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.

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

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

Output:

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