PostgreSQL sum function

PostgreSQL sum function returns the cumulative value of the expression.

Syntax of the sum function in PostgreSQL

SELECT sum(aggregate_expression_id)
FROM tabs
[WHERE conds];

Or the syntax of the sum function when grouping results in one or more columns:

SELECT expression1_id, expression2_id,. expression_n_id,
SUM(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY expression1_id, expression2_id, expression_n_id;

Parameters and arguments of the function

• expression1id, expression2_id,… expression_n_id – Expressions that are not encapsulated in the sum function and must be included in the GROUP BY operator at the end of the SQL query.
• aggregate_expression_id – This is the column or expression to be summed up.
• Tabs – The tables from which you want to get the records. At least one table must be specified in the FROM operator.
• WHERE conds – Optional. These are the conditions that must be met to select records.

The sum function can be used in the following PostgreSQL versions

PostgreSQL 11, PostgreSQL 10, PostgreSQL 9.6, PostgreSQL 9.5, PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4.

Single Expression Example

Consider some examples of the sum function to understand how to use the sum function in PostgreSQL.
For example, you might want to know the total number of all the quantities in an inventory table for which the product_type ‘Hardware’

SELECT sum(quantity) AS "Total Quantity"
FROM inventory
WHERE product_type = 'Hardware';

In this example of the sum function, we called the expression sum(quantity) as “Total Quantity”. The result is that “Total Quantity” will be displayed as the name of the field when the result set is returned.

Example using DISTINCT

You can use the DISTINCT operator inside the sum function. For example, the SQL statement below returns a cumulative total salary with unique values of salary, where salary exceeds 38000$ per year.

SELECT sum(DISTINCT salary_id) AS "Total Salary"
FROM empls
WHERE salary_id > 38000;

If the salary were $82000 per year, only one of these values would be used in the sum function.

Example using the formula

The expression contained in the sum function does not necessarily have to be a single field. You can also use a formula. For example, you can calculate the total commission.

SELECT sum(sales * 0.05) AS "Total Commission"
FROM orders;

Example using GROUP BY

In some cases, you will need to use the GROUP BY operator with the sum function.
For example, you can also use the sum function to return the department and sum(quantity) (the total amount in a related department).

SELECT department, sum(quantity) AS "Total Quantity"
FROM inventory
GROUP BY department;

Since your SELECT operator has one column that is not encapsulated in the sum function, you must use the GROUP BY operator. That is why the department field shall be specified in the GROUP BY operator.

PostgreSQL: Sum | Course