Scaler
Academy
Data Science
Neovarsity
New
Skill Test
Free Masterclass
Search for Articles, Topics
SQL SUM() Function With Practical Examples

SQL SUM() Function With Practical Examples

By Sejal Somani
4 mins read
Last updated: 4 May 2023
170 views
Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Start Learning View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

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 8585% 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.

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.

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.

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.