Preamble
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
About Enteros
Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Database Performance with Enteros and AWS Resource Groups: A RevOps Approach to Streamlined Efficiency
- 13 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enhancing Healthcare Data Integrity: How Enteros, Logical Models, and Database Security Transform Healthcare Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…