PostgreSQL Sum() Function

The SUM() function in PostgreSQL is an aggregate function that returns the summed value of an expression. For example, we commonly use this function to calculate the sum of numerical values or columns.

In this tutorial, we will learn how to work with the sum() function in PostgreSQL and discover some basic examples on how to use it in an actual database.

PostgreSQL Sum() Function

The syntax of the sum() function in PostgreSQL is as follows:

SUM(DISTINCT expression)

The function ignores NULL. Hence, the sum() function doesn’t consider the NULL in the calculation.

If you use the DISTINCT option, the function only considers the distinct values and ignores the duplicates.

NOTE: Using the sum() function in a select statement returns NULL or Non-Zero if the select statement returns no rows.

PostgreSQL Sum() Function Example

Let us start by creating a table that holds the sales data as shown in the following query:

CREATE TABLE sales (

id SERIAL PRIMARY KEY,

product_name VARCHAR(50),

quantity INTEGER,

price NUMERIC(10, 2)

);

In the provided table, we get the following:

The id column is an auto-incrementing serial column and serves as the primary key.
The product_name column stores the VARCHAR data up to 50 characters which represents the name of the product.
The quantity is an INTEGER column that stores the quantity of the sold product.
Finally, the price column stores the numeric types to hold the price of the products.

We can then insert some sample data into the table as demonstrated in the following:

INSERT INTO sales (product_name, quantity, price) VALUES

('Product A', 5, 10.50),

('Product B', 3, 15.75),

('Product C', 2, 20.00),

('Product A', 2, 10.50),

('Product B', 4, 15.75);

We can use the sum() function to calculate the sum of the quantity column as shown in the following example:

SELECT sum(quantity) FROM sales;

Result:

sum
-----
16
(1 row)

You can also use the sum() function in conjunction with a GROUP BY clause as shown in the following:

SELECT product_name, sum(quantity) FROM sales group by product_name;

Result:

product_name | sum
--------------+-----
Product B | 7
Product A | 7
Product C | 2
(3 rows)

To use the sum() function in a “having” clause, you can run the query as follows:

SELECT product_name, sum(quantity) FROM sales group by product_name having sum(price) > 30;

This should return the product name and the total quantity for any product with a higher price than 30.

Output:

product_name | sum
--------------+-----
Product B | 7
(1 row)

Conclusion

We discussed how we can work with the sum() function in PostgreSQL to determine the total of a given table column.

PostgreSQL Sum() Function

PostgreSQL Sum() Function

PostgreSQL Sum() Function Example

Conclusion

About the author

John Otieno