SQL SUM() Function With Practical Examples
FREE
Overview
SQL allows us to do more than merely select data or expressions from tables. To compute data, aggregate functions such as SUM() are frequently employed in relational database operations. When a definite sum of a set of values is required, one can use this. For instance, you may use the SUM() function to determine how many students at a certain institution receive academic results of more than % in order to choose the best institutions.
Introduction to SUM() in SQL
The SUM aggregate function returns the sum of the values of expressions. An expression is typically the name of a field in the numerous rows returned by a query (or an expression that contains one or more field names).
- Sum in sql can be used in a SELECT query or subquery that references a table or view.
- SQL sum can appear in a SELECT list or a HAVING clause alongside ordinary field values.
- SQL sum function cannot be used in the ON clause of a JOIN unless the SELECT is a subquery.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Syntax
The SQL SUM() function returns the sum of numbers. The syntax of the SUM() function is as follows:
Parameters/Arguments
In this syntax
- The ALL method directs SUM() to return the total of all values, including duplicates. ALL is applied to all values.
- SUM() is instructed by the parameter DISTINCT to compute the sum of the solely unique entries.
- Expression refers to any legal phrase that yields a precise or close numerical value. Please take note that the expression does not support aggregate functions or subqueries.
NULL values are disregarded by the SUM() function.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Return Value
- For an expression having data type INT, SMALLINT, or TINYINT, SUM yields data type INTEGER.
- For an expression of data type BIGINT, SUM in sql delivers data of data type BIGINT.
- When an expression has data type DOUBLE, SUM delivers data of type DOUBLE.
- SUM delivers data of the data type NUMERIC for all other numeric data types.
Remarks
- The SUM() function only takes expressions that evaluate to numerical values, unlike other SQL aggregation methods.
- Non-numerical values, such as the empty string, are treated as zero by SUM.
Turn Learning into Career Growth
Examples
- Simple SUM
Output
- SUM with WHERE
Output
- SUM with Formula
Output
- SUM with GROUP BY
Output
Advanced SQL SUM() Function Examples
Sum with inner join
Table 1: Orders
Output
Table 2: Prices
Output
To find the total amount per order id, the following query can be used:
Output
ALL vs. DISTINCT
To illustrate the difference between ALL and DISTINCT using sum in sql, let's construct a table:
OUTPUT
- The total of All values in the VALUE column is returned using the following statement:
Output
To compute only distinct numbers using SQL SUM(), use the DISTINCT keyword. That is, if there are any duplicate values, they are treated as a single value. In this case, the value 56 appears for two products but is only added once when the DISTINCT keyword is used.
Output
Conclusion
- To add up the values in a certain column and return the sum, use the sql SUM function.
- The SUM function may be used as an analytical function that adds the numbers for particular groups together or as an aggregate function that adds all the values together.
- To compute only distinct numbers, combine the DISTINCT keyword with SUM(). In other words, duplicate values are considered as a single value.
Related Topics
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more