By the end of this post, you will learn how to use the SUM() with the GROUP BY clause. We will understand how GROUP BY and the SUM() work. Then, we will give examples of combining the two. Let’s begin!
Understanding SUM() and GROUP BY
Suppose you have your PostgreSQL database. You can create a table that you wish to manipulate and perform different tasks using different clauses and aggregate functions. We use the following employee table throughout the post.
The SUM() is an aggregate function that is used with your table when selecting different columns to return the sum of the selected values. The following example demonstrates how you can use the SUM()
to sum the values of your columns. We summed up the salary and allowance columns in this case.
For the GROUP BY clause, it divides the selected rows into groups. The following example groups the salary, dept, and salary columns that we selected in the query.
Having understood how GROUP BY and SUM() work in PostgreSQL, let’s see how to combine them.
PostgreSQL SUM with GROUP BY
When grouping different rows that are returned by the SELECT statement, you can include the SUM() to sum up different values in the selected column. In this example, we select the “dept” column and use the sum function to add the values of the salary column.
Our output shows the dept column that we selected and used in the GROUP BY clause and the total for each dept value:
Suppose we want to get the total allowances for the different status values in our table. We could select the status column, then sum the allowance column. Moreover, we must specify the selected column in the GROUP BY clause.
Postgres returns the two columns that display the unique values and the sum of the allowances for the values. Ideally, we return the sum of allowances for each group.
Still, you can use the ORDER BY clause to order the output of the GROUP BY clause. For instance, in our earlier example where we grouped the “dept” column to get the sum of the salary values, we can order the output in ascending order.
Use the ORDER BY clause and specify the target column for the ordering and the order type, ascending or descending.
You can also order the grouped rows using one of the selected columns. The following example selects the dept and status column, sums the allowance column, and groups the row using the selected columns but orders the grouped rows using the dept column.
We can see that the sum values are ordered based on the specified row, unlike how we ordered it in ascending order in the previous example.
That’s how you use the SUM() with the GROUP BY clause when working with PostgreSQL.
Conclusion
In PostgreSQL, the GROUP BY clause helps group the rows based on certain criteria. You can add an aggregate function such as SUM() to sum the values in the selected rows. The selected columns must be specified in the GROUP BY clause. We presented the GROUP BY and the SUM() in PostgreSQL. Moreover, we’ve seen the different examples on how to combine the two when executing different queries. Hopefully, you now understand how to use the PostgreSQL SUM() with the GROUP BY clause.
