SUM function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PostgreSQL SUM function

SUM function

The SUM function in PostgreSQL returns the sum of values for the list of selected columns.

Syntax

SUM (* | [DISTINCT] ALL | column_name)

Parameters

Name Description
column_name Name of the column
* The asterisk(*) indicates all the rows.
DISTINCT This clause is optional. It indicates uniqueness.
ALL This clause is optional. It is default clause.

Contents:

  • PostgreSQL SUM function example
  • PostgreSQL SUM as a level
  • PostgreSQL SUM DISTINCT
  • PostgreSQL SUM WHERE clause
  • PostgreSQL SUM with GROUP BY
  • PostgreSQL SUM with WHERE CLAUSE and GROUP BY
  • PostgreSQL SUM with HAVING CLAUSE and GROUP BY
  • PostgreSQL SUM with GROUP BY and ORDER BY

PostgreSQL SUM function example

If we want to calculate the total sum of salary for all employees in the employee table, the following SQL can be used.

SQL

SELECT SUM(salary) 
FROM employee;

Pictorial Presentation of PostgreSQL SUM()

PostgreSQL SUM as a level

If we want to calculate the total sum of salary for all employees and show the result against 'Total Salary' head in the employee table, the following SQL can be used.

SQL

SELECT SUM(salary)  AS "Total Salary"
FROM employee;

PostgreSQL SUM DISTINCT

If we want to get the sum of unique salary from employee table, the following SQL can be used.

SQL

SELECT SUM ( DISTINCT salary)
FROM employee;

Explanation

The above example shows that the sum of salary is smaller than the sum salary for all employees because the DISTINCT clause eliminates the repetition of each salary and summed only once.

PostgreSQL SUM WHERE clause

If we want to get the sum of unique salary from employee table who belongs to the deptno 15, the following SQL can be used.

SQL

SELECT SUM ( DISTINCT salary) AS "Total Salary"
FROM employee
WHERE deptno=15;

 

PostgreSQL SUM with GROUP BY

The following statement will return the designation and total salary against each designation from the employees table.

SQL

SELECT job_id,SUM(salary) AS "Total Salary" 
FROM employees 
GROUP BY job_id;;

 

Output :

   job_id   | Total Salary
------------+--------------
 AC_ACCOUNT |      8300.00
 ST_MAN     |     36400.00
 IT_PROG    |     28800.00
 SA_MAN     |     61000.00
 AD_PRES    |     24000.00
 AC_MGR     |     12000.00
 FI_MGR     |     12000.00
 AD_ASST    |      4400.00
 MK_MAN     |     13000.00
 PU_CLERK   |     13900.00
 HR_REP     |      6500.00
 PR_REP     |     10000.00
 FI_ACCOUNT |     39600.00
 SH_CLERK   |     64300.00
 AD_VP      |     34000.00
 SA_REP     |    250500.00
 ST_CLERK   |     55700.00
 MK_REP     |      6000.00
 PU_MAN     |     11000.00
(19 rows)

PostgreSQL SUM with WHERE CLAUSE and GROUP BY

The following statement will return the designation wise total salary for that designation which salary does not exceed 12000 and above, from employees table.

SQL

SELECT job_id,SUM(salary) AS "Total Salary" 
FROM employees 
WHERE salary<12000 
GROUP BY job_id;

Output :

   job_id   | Total Salary
------------+--------------
 AC_ACCOUNT |      8300.00
 ST_MAN     |     36400.00
 IT_PROG    |     28800.00
 SA_MAN     |     21500.00
 AD_ASST    |      4400.00
 PU_CLERK   |     13900.00
 HR_REP     |      6500.00
 PR_REP     |     10000.00
 FI_ACCOUNT |     39600.00
 SH_CLERK   |     64300.00
 SA_REP     |    250500.00
 ST_CLERK   |     55700.00
 MK_REP     |      6000.00
 PU_MAN     |     11000.00
(14 rows)

PostgreSQL SUM with HAVING CLAUSE and GROUP BY

The following SQL statement will return, designation wise total employees and total salary for that designation which salary does not exceed 12000 and above and a minimum of five employees have this designation.

SQL

SELECT job_id,COUNT(*) AS "Number of Employees", 
SUM(salary) AS "Total Salary" 
FROM employees 
WHERE salary<12000
GROUP BY job_id
HAVING COUNT(*)>=5;

Output :

   job_id   | Number of Employees | Total Salary
------------+---------------------+--------------
 ST_MAN     |                   5 |     36400.00
 IT_PROG    |                   5 |     28800.00
 PU_CLERK   |                   5 |     13900.00
 FI_ACCOUNT |                   5 |     39600.00
 SH_CLERK   |                  20 |     64300.00
 SA_REP     |                  30 |    250500.00
 ST_CLERK   |                  20 |     55700.00
(7 rows)

PostgreSQL SUM with GROUP BY and ORDER BY

The following statement will return, the designation wise total employees and total salary, arranged in descending order according to the total number of employees, for that designation which salary does not exceed 12000 and above and a minimum of five employees have this designation.

SQL

SELECT job_id,COUNT(*) AS "Number of employees" ,
SUM(salary) AS "Total Salary"
FROM employees 
WHERE salary<12000
GROUP BY job_id
HAVING COUNT(*)>=5
ORDER BY COUNT(*) DESC;

Output :

   job_id   | Number of employees | Total Salary
------------+---------------------+--------------
 SA_REP     |                  30 |    250500.00
 SH_CLERK   |                  20 |     64300.00
 ST_CLERK   |                  20 |     55700.00
 ST_MAN     |                   5 |     36400.00
 FI_ACCOUNT |                   5 |     39600.00
 IT_PROG    |                   5 |     28800.00
 PU_CLERK   |                   5 |     13900.00
(7 rows)