# Postgres Sum Function in SQL

Get Started >>

## 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

 1 i_sum = SUM(set_of_numbers);

## 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.

id_technologyt_namei_people_know_it
0Python6
1Java1
2C#3
3JavaScript4

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:

 123456 SELECT         SUM(i_people_know_it)     FROM         coding-languages     GROUP BY         i_people_know_it;

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:

idt_name_usert_name_techt_category_techi_rate
0JimmyMongoNoSQL2
1FrankMongoNoSQL8
2BettyMySQLRDB4
3GabeMySQLRDB6
4TomPostgreSQLRDB8
5PhilPostgreSQLRDB10
6HalPostgreSQLRDB9
7WandaPythonLang6
8KenPythonLang10
9RobertPHPLang4
10PaulPHPLang6

#### 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).
 1234567891011 SELECT         t_name_tech         , SUM(i_rate) AS i_rate_average     FROM         technologies     GROUP BY         t_name_tech         , i_rate;     HAVING         t_category_tech = 'Lang'         AND t_name_tech = 'Python';

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:

t_name_techt_category_techi_rate
PythonLang16

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:

 1234567891011 SELECT         t_name_tech         , SUM(i_rate) AS i_rate_average     FROM         technologies     GROUP BY         t_name_tech         , i_rate;     HAVING         t_category_tech = 'Lang'         AND t_name_tech = 'PHP'; -- THE ONLY CHANGE

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.

Rate     ## Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

## Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.