Introduction to PostgreSQL SUM()
PostgreSQL sum() function is used to return the distinct value or sum of values, the sum function ignores null values from a table column. It states that the null value from the column will not be considered in the sum function, if we have used a distinct operator using the sum function then the sum function is only considered the distinct values from the table column on which we have used the Sum function. Sum function in PostgreSQL is very useful and important to return the sum of all values from the column or the sum of distinct values from the column. Sum function is used to get the addition of a numeric column.
Syntax
Below is the syntax of the sum function in PostgreSQL.
1. Sum (Distinct column_name (Name of a column which was used with sum function to get the addition of values.))
OR
2. Select column_name1, column_name2, …, sum (column_name or aggregate expression) from table_name [ where condition ] group by column_name;
OR
3. Select sum (column_name or aggregate expression) from table_name [ where condition]
OR
4. Sum (column_name (Name of the column which was used with sum function to get the addition of values.))
Below is the parameter description syntax of the sum function in PostgreSQL.
1. Sum – This function is used to get the addition of all values from a numeric column in PostgreSQL. When we want the addition of the numeric table column then we have used the sum function in PostgreSQL.
2. Distinct – This keyword is used to get the addition of distinct values for the numeric column in PostgreSQL. It is an optional parameter of the sum function in PostgreSQL.
3. Column name – It is defined as the name of the column on which we have performed a sum operation to get the addition of all values. The column name is a very important parameter in the sum function.
4. Table name – It is defined as the name of the table from which column we have performed sum operation and get the result of the addition of all values. We can use a numeric column table name to use the sum function in PostgreSQL.
5. Select – This operation is used to retrieve data from the table. We have to retrieve data using the sum function in PostgreSQL. We have used the sum function using select operations.
6. Where condition – Using the where condition we have filtered the result of select operations in PostgreSQL. We have got specific results by selecting conditions using the where clause in PostgreSQL.
7. Group by – We have used group by clause with sum function in PostgreSQL. We have to retrieve results as per group by the condition in PostgreSQL.
How SUM() function works in PostgreSQL?
- Below is the working of the sum function in PostgreSQL. We have used the sum function to get the sum of the numeric column in PostgreSQL.
- Sum function will exclude the null values from the column if the column contains the null value and we have to perform the sum function operation on the same then sum function will exclude the null values from the column.
- After excluding null values it will provide the result of the sum function. The below example shows that the sum function will exclude the null values from the column.
select * from emp_test;
select sum(salary) from emp_test;
- In the above example, we have performed sum function operation on the salary column, after performing sum function operation on the salary column it will return the sum of all values from the salary column.
- At the time of performing sum operation, it will exclude the null values from the salary column in PostgreSQL.
- If we have used a distinct clause with sum function it will only get the sum of distinct values in PostgreSQL.
- In the below example we have used the sum function with a distinct clause, it will only return the sum of the result of the column which contains only the distinct values.
select * from emp_test;
select sum(distinct salary) from emp_test;
- In the above example, we have used the sum function on the salary column with a distinct clause. Distinct clause only returns the result of only emp id of 1, 2, and 3 because that value is distinct in the salary column.
- We have used sum function in an application where we have to retrieve data of the sum of calculation of the result.
- Column name and table name is the important parameter of sum function.
- We have used some functions with where having and group by clause.
- If we want the sum of unique records from the PostgreSQL table then we have used the sum function with where clause.
Examples of PostgreSQL SUM()
- Below is the example of the sum function.
- We have used emp_test to describe the example of the sum function in PostgreSQL is as follows.
- Below is the data and table description of the emp_test table.
select * from emp_test;
\d+ emp_test;
Example #1 – Sum function without using any clause
In the below example, we have used the sum function without using any clause. We have applied the sum function on the salary column.
select sum (salary) from emp_test;
Example #2 – Sum function using distinct clause
In the below example, we have used the sum function with a distinct clause. We have applied the sum function with a distinct clause on the salary column.
select sum (distinct salary) from emp_test;
Example #3 – Sum function using group by clause
In the below example, we have used the sum function with the group by clause. We have applied the sum function the salary column and group by clause on the emp_name column.
select emp_name, sum (salary) from emp_test group by emp_name;
Example #4 – Sum function using where condition
In the below example, we have used sum function with the where condition. We have applied sum function on the salary column and where condition on emp_name column.
select sum (salary) from emp_test where emp_name = 'ABC';
Output:
Recommended Articles
This is a guide to PostgreSQL SUM(). Here we discuss How the SUM() function works in PostgreSQL along with the Syntax and Examples. You may also have a look at the following articles to learn more –
