How to Use SUM() Function with Group By Clause in PostgreSQL

In Postgres, it is usually the case that we want to get the data of one category from different records. This was never simple before the GROUP BY clause came to the rescue. The GROUP BY statement makes groups of the given data by some specified column or columns.

The GROUP BY is usually found complementing the aggregate functions to give useful results. The prime use case for the situation when the GROUP BY clause and SUM() function are used together is when we want to get the sum of all the prices belonging to the same products sold till now.

In this article, we will see how can we use the GROUP BY clause with the SUM() function.

How to Use SUM() Function with Group By Clause in PostgreSQL?

The GROUP BY() clause works as a conjunction when it is being used with the aggregate functions. We will discuss how the SUM() function and the GROUP BY clause are used together. The SUM() function is a built-in function that returns the sum of the given parameter it is usually used with the GROUP BY clause. The basic syntax for the query can be written as:

SELECT col_name, SUM (exp)
 FROM tab_name
 GROUP BY col_names;

In the above syntax:

● With the SELECT statement, we specify the column names we want to get in return for the execution of the query.

● The SUM() function takes in the parameters and returns their sum.

● After the GROUP BY clause, the name of the column is specified.

Let’s consider an example that brings more clarity to the topic.

Example: SUM() Function With GROUP BY Clause

Let's consider the table named “store_sales_details”. The table looks like this:

Now if we want to group the data by product present in the table. We will write the query as:

SELECT
  product,
  SUM (price) AS total_price
 FROM   store_sales_details
 GROUP BY product;

The above code says:

● The table will return the product column and another column that sums the price. This column will be named as total_price.

● The table is grouped by the product column.

● In short, the query returns the sum of all the prices of the same products.

This means that the GROUP BY clause groups the table by product and sums up the prices that belong to the same product in a separate column named “total_price”.

The output looks like this:

The output advocates the concept that we have discussed above.

We can also apply some ordering or limit to the queries with the SUM() function and GROUP BY function.

Like this:

SELECT
product,
SUM (price) AS total_price
FROM store_sales_details
GROUP BY product
ORDER BY total_price DESC;

The query performs the same function as the above one and additionally, it will order the product prices in descending order. The output looks like this:

So this is how we use the SUM() function and the GROUP BY function together.

Conclusion

GROUP BY clause is used to group the data and it is most fruitful when it is used with any aggregation function. In this article, we particularly focused on the SUM() function used with the GROUP BY clause. This combination can be used to retrieve the sum of the same category of data (as we have seen in the example).

How to Use SUM() Function with Group By Clause in PostgreSQL