Postgres Sum Function in SQL

Introduction

In this tutorial, we’ll learn how to use the Postgres Sum function in SQL to find the sum of a set of values, including:

• What? What does the SUM function do when given a set of numbers?
• How? How do we best use this function in our PostgreSQL SQL commands?
• Why? When would we make use of this function? We’ll practice with a real world use case. Includes use of Postgres’ “GROUP BY” and “HAVING” clauses.

Prerequisites

• A beginner understanding of writing SQL for Postgres (or Oracle, MySQL, MS SQL Server, etc.) using the free PG Admin tool or another database admin tool, or by with script or code – compiled or not, object-oriented or not – programming languages like Java, Python, C#, PHP, ASP.Net, Javascript, VB.Net, Ruby, Node, etc.) that provide a database connection, as well as a method for sending PL/SQL queries to our database, to get data or make changes to your data.
• Basic understanding of the use of common SQL (PL/SQL) statements, including SELECT, FROM, and GROUP BY. Optional knowledge: HAVING clause.
• Understanding of what string and function are and how they work.

What is the SUM() function?

The PostgreSQL SUM function gives us the sum of a set of numbers.

How do we use the function in SQL?

Syntax

Why and when do we use the SUM() function?

Let’s begin with an easy example

Our initial dataset is a table called coding_languages.

The _i_peopleknow column represents the number of employees in your company who know this language. We want to get a sum total of all the people in the company who know these four languages. This probably isn’t a thing you ever want to know unless you place some constraint on the data, like what language (using the HAVING statement), but this initial exercise doesn’t have to be that realistic.

note for example below. This is where we use SQL GROUPing. You can think of it this way: If you want to sum more than one value, you need to look at the group of values, since there is no point to retrieving the sum of one number.

Let’s write the SQL script to use the SUM function to retrieve the sum we want from our recordset:

How does that SQL code we just wrote operate? We’ll take it step by step:

• (1) SELECT: We are relaying to Postgres’ SQL interpretor that we want a sum of all values (because there is no HAVING statement) in the i_people_know_it field (column).
• (2) FROM: Specifying the coding-languages table to get our data from.
• (3) GROUP BY: Telling PostgreSQL that we are grouping. As mentioned above, the SUM() function REQUIRES grouping of our data.

The above query returns the integer, “14”.

Now that you understand the basics of how SUM works, we’ll go full “real world,” so you can gain a deeper understanding of Postgres’ SUM() function.

Real world use case of the “Sum” function

The project: We had employees assess the technologies used at our company and want to know the sum of all ratings. Here we will explore another relatively simple use of SUM() while introducing you to the “HAVING” clause. Here’s the full data set with a table name of technologies:

Our plan – Create a query to do the following

Sum up the total number of ratings given to the Python language.

• (1) Consider only the rows in our data set where the _t_categorytech column value is equal to “Lang”. This is where the HAVING clause is necessary. This filters our returned rows down to four.
• (2) Return the integer in the _t_nametech column (field) for those four rows because we want to know which technologies the ratings are related to.
• (3) Not caring who declared the rating, so no mention of the _t_nameuser column (field) is needed.
• (4) Get a sum of the values of those 4 rows using the “i_rate” column (field).
• (5) Group the appropriate columns (using GROUP BY).

Analysis of the SQL above:

• (1) SELECT: This tells PostgreSQL (a) the two columns we want returned; and (b) that we want a sum of all values (limited by the HAVE statement below) in the i_rate field and to name that result as i_rate_average.
• (2) FROM: Setting the technologies table as the source for our query.
• (3) GROUP BY: Notifying Postgres that we want to group. The SUM function will only work if we group the data that PostgreSQL is adding up.
• (4) HAVING: This tells Postgres to only use the rows that have a value of “Lang” in the t_category_tech field and having “Python” in the t_name_tech column.

Here is the data set that will be returned when we run that SQL above:

The next step might be for you to now find out a SUM() of ratings for PHP at the company so you can compare overall sums of how your employees feel about the two languages. To do that, we would merely change the HAVING clause, so our query looks like:

Note: a more complex query could give us a comparison so that we would not have to run two queries, but that is a topic for another article.

Miscellany

• We encourage you to seek out our other articles that use the simple and useful SUM Postgres function, along with other, related functions like AVG and COUNT.
• Are you curious why we named some of our variables and columns with a prefix of “i” or “t“? In this case, we use “i” to mean integer and “t” to mean text or string. Here is an article on that topic.

Conclusion

In this article, we learned how to use the SUM() function in Postgres. We even used a real world example, so as to help you see how powerful SUM can be when used in conjunction with GROUP BY and HAVING SQL clauses.