PostgreSQL Compute Total Sum of Column

In this PostgreSQL tutorial, I will show you how to compute the total sum of columns in PostgreSQL.

You will understand the function SUM() in this tutorial to compute the sum of all the values in a column. Also, I will explain to you the syntax of the SUM() function and how to use it.

Finally, you will implement the SUM() function with clauses like HAVING and GROUP with practical examples.

PostgreSQL Compute Total Sum of Column

PostgreSQL provides an aggregate function that is called SUM(), which returns the total sum of the column or the sum of the values within the column. It is very helpful in decision making and today businesses usually use it.

Let me give you an example, Suppose a business wants to know the total sales, and for that, they will use the SUM() to add up the daily sales and based on the total sales they can make a decision whether the marketing strategy is effective or not.

Or maybe should a business invest in more stocks. This is one aspect of using the SUM() but there can be other aspects.

The syntax is given below for how to use the SUM() function in PostgreSQL.

SUM(column_name)

• SUM(): It is the function itself to compute the total sum of columns.
• column_name: The column on which the SUM() function is applied and returns the total sum of that column.

First, I will show you a simple example of using the SUM() function and then proceed towards a complex example like using the SUM() function on the table with different clauses of PostgreSQL.

PostgreSQL Compute Total Sum of Column using SELECT

Let’s say you have a table called sales with column id, product_name, sale_date and sale_amount and you have to compute the total sales, the table is shown below.

Use the SUM() function on the column sale_amount as shown in the below query.

SELECT SUM(sale_amount) as total_sum FROM sales;

The above query returns the total sum of column sale_amount as 8660.70 dollars using the SUM() function in the SELECT statement.

You have another task to compute total sales on a specific date 2023-01-22, for that filter that day and compute the total sales using the SUM() function.

Use the below query.

SELECT SUM(sale_amount) FROM sales
    WHERE sale_date = '2023-01-22';

The output shows that the total sales on 2023-01-22 are 1420.40 dollars, this is how to compute the total sum of the filtered data from the column.

PostgreSQL Compute Total Sum of Column with GROUP BY

Suppose there is an online store and the owner of the store wants you to find the total sales per product. To find the total sales on each product use the GROUP clause.

Use the below query to compute the total sales per product.

SELECT product_name, SUM(sale_amount) as total_sales 
FROM sales 
GROUP BY product_name;

The above output shows the total sales on each product such as the Charger made a sale of 20.15 dollars and the Desktop made 1100.00 dollars.

The above query first groups the products by their unique names using the GROUP BY product_name, and then on each group SUM() function is applied separately to compute the total sum of that group as you can see in the above output.

To know about the GROUP BY clause, refer to this tutorial Group By in PostgreSQL

PostgreSQL Compute Total Sum of Column with Having

The HAVING clause works as the WHERE clause in PostgreSQL but the HAVING clause works on the aggregated data or results, unlike the WHERE clause.

After computing the total sales on each product in the previous example, the owner of the store wants you to find the product with total sales of more than 1000 dollars.

So use the below query.

SELECT product_name, SUM(sale_amount) as total_sales 
FROM sales 
GROUP BY product_name
HAVING SUM(sale_amount) > 1000;

The above query filters the total sales of products exceeding the amount of 1000 dollars using the SUM() function with the HAVING clause. If you remove the HAVING clause you get the result same as you have found in the previous example.

So in this example, only the HAVING clause is used to filter product which has made sale of more than 1000 dollars, and there are four product Desktop, Camera, Mobile Phone and Laptop

Conclusion

In this PostgreSQL tutorial, you learned how to use the SUM() function in PostgreSQL to find the total sum of columns. Additionally, you learned about using the SUM() function with clauses such as HAVING and GROUP.

You may also read:

• How to Use Postgresql Having Clause
• PostgreSQL Average Count
• PostgreSQL Order By with Examples