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
, andGROUP 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_technology | t_name | i_people_know_it |
---|---|---|
0 | Python | 6 |
1 | Java | 1 |
2 | C# | 3 |
3 | JavaScript | 4 |
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:
1 2 3 4 5 6 | 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 thei_people_know_it
field (column). - (2)
FROM
: Specifying thecoding-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:
id | t_name_user | t_name_tech | t_category_tech | i_rate |
---|---|---|---|---|
0 | Jimmy | Mongo | NoSQL | 2 |
1 | Frank | Mongo | NoSQL | 8 |
2 | Betty | MySQL | RDB | 4 |
3 | Gabe | MySQL | RDB | 6 |
4 | Tom | PostgreSQL | RDB | 8 |
5 | Phil | PostgreSQL | RDB | 10 |
6 | Hal | PostgreSQL | RDB | 9 |
7 | Wanda | Python | Lang | 6 |
8 | Ken | Python | Lang | 10 |
9 | Robert | PHP | Lang | 4 |
10 | Paul | PHP | Lang | 6 |
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).
1 2 3 4 5 6 7 8 9 10 11 | 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 thei_rate
field and to name that result asi_rate_average
. - (2)
FROM
: Setting thetechnologies
table as the source for our query. - (3)
GROUP BY
: Notifying Postgres that we want to group. TheSUM
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 thet_category_tech
field and having “Python” in thet_name_tech
column.
Here is the data set that will be returned when we run that SQL above:
t_name_tech | t_category_tech | i_rate |
---|---|---|
Python | Lang | 16 |
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:
1 2 3 4 5 6 7 8 9 10 11 | 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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started