Postgresql Sum – How to use

In this tutorial, we will learn about “Postgresql sum” and cover the following topics.

• Postgresql sum multiple columns and group by
• Postgresql sum multiple rows
• Postgresql sum function
• Postgresql sum with condition
• Postgresql sum double precision
• Postgresql sum case
• Postgresql sum distinct
• Postgresql sum over(partition)

Before beginning, we need to know about the function named SUM(), with the help of the SUM function, we can add or sum the columns or rows values.

Let’s understand through an example that ‘How SUM function work’.

We are going to sum the population of major cities in the United Kindom using the table given below.

In the above table, city_name columns contain the name of all major cities in the United Kindom and the population related to that city.

Now, calculate the sum of all the population of cities in the United Kindom.

SELECT sum(population) as sum_population FROM major_cities;

The output shows that the sum of population is 1461233.

Postgresql sum multiple columns

In Postgresql, the sum function can be used to add multiple columns values.

Let’s create a table named sum_dummy with 4 columns col_1, col_2, col_3, col_4, and of type int, int, float, float respectively.

CREATE TABLE sum_dummy(col_1 int,col_2 int, col_3 float, col_4 float);

Insert the following records.

INSERT INTO sum_dummy(col_1,col_2,col_3,col_4)VALUES(1,2,4.33,5.10),
(5,6,7.455,9.345),(7,8,9.11,6.49),(10,15,14.123,19.777);

SELECT * FROM sum_dummy;

The output of the above code is given below.

Now, run the below code to add the columns col_1 and col_2 values.

SELECT SUM(COALESCE(col_1,0) + COALESCE(col_2,0)) FROM sum_dummy;

In the above code, we have used the sum function to sum columns col_1 and col_2.

Within the sum function first, we have applied the COALESCE function on each column to return the first non-null value in the column. After that, both the columns are added using the plus (+) operator before applying the sum function.

The output of the above code is given below.

In the above output, the sum of the multiple columns col_1 and col_2 is 54.

Read: PostgreSQL Date Difference

Postgresql sum multiple rows

In Postgresql, we can sum the multiple rows without using the sum function, by using the plus operator (+), we combine the multiple columns.

Let’s understand through an example.

SELECT col_1,col_2, col_2 + col_1 as sum FROM sum_dummy
GROUP BY col_1,col_2;

In the SELECT statement look at the first line where col_2 + col_1, here we are adding the values of col_2 and col_1 row-wise, then grouping it with col_1, col_2.

The output of the above code is given below.

In the above output, we have added the col_1 and col_2 values row-wise in a new column named sum.

Read: PostgreSQL list databases

Postgresql sum function

In Postgresql, the SUM function returns the sum of all values in a numeric column.

Let’s understand with an example.

SELECT SUM(col_1) FROM sum_dummy;

In the above code, we are using the sum function to add all the values of the column name col_1.

The output of the above code is given below.

Read: PostgreSQL Data Types

Postgresql sum with condition

In Postgresql, we can sum the column values based on some condition.

Let’s sum the col_1 column values greater than 5.

SELECT SUM(col_1) FROM sum_dummy
WHERE col_1 > 5;

In the above code, we are adding all values of the col_1 column which is greater than 5 using condition WHERE col_1 > 5.

The output of the code is given below.

In the above output, the sum of all the values in the col_1 column which is greater than 5 is 17.

Read: Postgresql create user with password

Postgresql sum double precision

In Postgresql, we can also add the double-precision or float values.

In the code below, we are adding the col_3 column values, the col_3 column contains the double-precision or float values.

SELECT SUM(col_3) FROM sum_dummy;

In the above code, we are adding all values of column col_3 using the SUM function within the SELECT statement.

The output of the above code is given below.

In the above output, the sum function also returns the sum in double precision or float, and that is 35.018.

Read: How to set user password in PostgreSQL

Postgresql sum case

In Postgresql, the SUM function can be used with CASE or we can sum the values based on the CASE statement.

The table we will use in this tutorial is emp_info and the description of the table is given below.

Let’s run the below query to sum the salary of employees whose salary is greater than 2800.

SELECT sum(CASE WHEN salary_$ > 28000 THEN salary_$ ELSE 0 END) from emp_info;

In the above code, the first CASE statement is returning the salary greater than 28000 within the SUM function, then the SUM function is applied to the result returned by the CASE statement.

The output of the above code is given below.

Read: Postgresql ilike case insensitive

Postgresql sum distinct

In Postgresql, we can also sum the unique values in the column, sometimes a column can contain duplicate values. So to sum only the unique or the values that are not duplicated, we use the distinct function.

First, let’s insert the sum duplicate values in the table sum_dummy that we have created in the first sub-section of this tutorial.

INSERT INTO sum_dummy(col_1)VALUES(1),
(5);
SELECT * FROM sum_dummy;

Run the below query to sum the distinct values of the column.

In the above output, column col_1 contains duplicate values 1, 5.

Let’s calculate the sum of values that are distinct in col_1.

SELECT SUM(DISTINCT(col_1)) FROM sum_dummy;

In the above code, first, we are selecting the unique values from column col_1 using the DISTINCT function, summing the result of a distinct function using the SUM function.

The output of the above code is given below.

Read: Postgresql group_concat

Postgresql sum over(partition)

In Postgresql, we can sum the column values using the WINDOW function that is OVER. The OVER clause decides exactly how the rows of the query are split up for the processing by the window function.

The table we will use in this section is emp_info that contains the information about the employees like name, age, salary, address. The description of the table is given below.

Let’s run the below code to compare each employee’s salary with the sum salary based on his or her address.

SELECT address, name, age, sum(salary_$) OVER (PARTITION BY address) FROM emp_info;

The output of the above code is given below.

Also, take a look at some more PostgreSQL articles.

• Postgresql if else
• PostgreSQL Loop
• Postgresql unique
• Postgresql Average
• Postgresql while loop
• Drop Database PSQL
• Postgres date range
• Postgresql REGEXP_REPLACE

So, in this tutorial, we have learned about “Postgresql sum“. And we have also covered the following topics.

• Postgresql sum multiple columns and group by
• Postgresql sum multiple rows
• Postgresql sum function
• Postgresql sum with condition
• Postgresql sum double precision
• Postgresql sum case
• Postgresql sum distinct
• Postgresql sum over(partition)