PostgreSQL SUM Function

Summary: this tutorial shows you how to use PostgreSQL SUM function to calculate the sum of values or distinct values.

Introduction to PostgreSQL SUM function

The PostgreSQL SUM function returns the sum of values or distinct values. The syntax of the SUM function is as follows:

There are some important points you should remember when using the SUM function:

• The SUM function ignores NULL values. It means the NULL values are not considered in calculation of the SUM function.
• If you use DISTINCT operator, the SUM function only calculates the values of distinct values. For example, without the DISTINCT operator, the SUM function returns 12 when calculating the sum of 1, 1, 8, and 2. However, with DISTINCT operator, the SUM function will return 11  (1 + 8 + 2).
• If you use the SUM function in a SELECT clause, it returns a NULL value instead of zero (0) in case the SELECT statement returns no rows.

PostgreSQL SUM function examples

We will use the payment table in the sample database to demonstrate the functionality of the SUM function.

Let’s calculate the sum of payment paid by customer id 2000.

Because the condition in the WHERE clause filters all rows, the SUM function returns a NULL value as mentioned above. No row exists in the payment table with customer_id 2000.

Sometimes, you want the SUM function returns zero instead of a NULL value in case there is no matching row found by the SELECT statement. In this case, you use the COALESCE function that returns the first argument as a default value if the second argument is NULL.

The following query illustrates the SUM function with the COALESCE function:

PostgreSQL SUM function with GROUP BY clause

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 for each group.

For example, to calculate the total amount paid by each customer, you use the SUM function with the GROUP BY clause as follows:

The following query returns top 5 customers who paid the most:

The following diagram illustrates how PostgreSQL performs the query:

PostgreSQL SUM function with HAVING clause

To filter the sums of groups based on a specific condition, you use the SUM function in the HAVING clause. For example, the following query returns only customers who paid more than $200:

In this tutorial, you have learned how to use the PostgreSQL SUM function to calculate the total of values or distinct values.