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)

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.