SQL SUM() function

SUM() function

The SQL AGGREGATE SUM() function returns the SUM of all selected column.

Syntax:

SUM ([ALL | DISTINCT] expression )

DBMS Support : SUM() function

DB2 and Oracle Syntax :

 SUM ([ALL | DISTINCT] expression ) OVER (window_clause)

Parameters:

Syntax diagram - SUM() function

SQL SUM() on specific column example

To get the total SUM of 'advance_amount' of the 'orders' table, the following SQL statement can be used :

Sample table: orders

SQL Code:

-- Calculating the sum of the 'advance_amount' column
-- From the 'orders' table
SELECT SUM(advance_amount)
-- Result: Total sum of 'advance_amount' in the 'orders' table
FROM orders;

Explanation:

• SELECT SUM(advance_amount): This is the main part of the SQL query. It uses the SUM() function to calculate the sum of all values in the 'advance_amount' column of the 'orders' table. The result will be a single row with a single column containing the total sum of all advance amounts in the 'orders' table.


• FROM orders: This specifies the source of the data for the query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'orders' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

SUM(ADVANCE_AMOUNT)
-------------------
              19450

Pictorial Presentation:

SQL SUM() using multiple columns example

To get the sum of 'opening_amt' and 'receive_amt' from the 'customer' table, the following SQL statement can be used:

Sample table: customer

SQL Code:

-- Calculating the sum of the sum of 'opening_amt' and 'receive_amt' columns
-- From the 'customer' table
SELECT SUM(opening_amt + receive_amt)
-- Result: Total sum of the sum of 'opening_amt' and 'receive_amt' in the 'customer' table
FROM customer;

Explanation:

• SELECT SUM(opening_amt + receive_amt): This is the main part of the SQL query. It calculates the sum of the result of adding the 'opening_amt' and 'receive_amt' columns for each row in the 'customer' table using the SUM() function. The + operator is used to add the values of the 'opening_amt' and 'receive_amt' columns together. The result will be a single row with a single column containing the total sum of the calculated values for all rows in the 'customer' table.


• FROM customer: This specifies the source of the data for the query, which is the 'customer' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'customer' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

SUM(OPENING_AMT+RECEIVE_AMT)
----------------------------
                      353000

SQL SUM() with where

In the following example, we have discussed usage of WHERE clause along with the SQL SUM() function to sum one or more columns against one or more conditions.

Example:

To get the total SUM of 'advance_amount' of the 'orders' table with the following condition -

1. 'agent_code' must be 'A003',

the following SQL statement can be used :

Sample table: orders

SQL Code:

-- Calculating the sum of the 'advance_amount' column
-- From the 'orders' table
SELECT SUM(advance_amount)
-- Result: Total sum of 'advance_amount' in the 'orders' table
FROM orders
-- Filtering the results to include only rows where the 'agent_code' is equal to 'A003'
WHERE agent_code = 'A003';

Explanation:

• SELECT SUM(advance_amount): This is the main part of the SQL query. It uses the SUM() function to calculate the sum of all values in the 'advance_amount' column of the 'orders' table. The result will be a single row with a single column containing the total sum of all advance amounts in the 'orders' table.


• FROM orders: This specifies the source of the data for the query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'orders' table.


• WHERE agent_code = 'A003': This clause filters the rows from the 'orders' table. It restricts the calculation of the sum to only include rows where the value in the 'agent_code' column is 'A003'. This condition ensures that only orders handled by the agent with code 'A003' are considered in the sum calculation.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

SUM(ADVANCE_AMOUNT)
-------------------
               1000

SQL SUM() with COUNT()

In the following example, we have discussed the usage of SQL SUM() and SQL COUNT() together in a SQL SELECT statement. Regarding this, it should be mentioned that the SQL SUM() and SQL COUNT() both returns a single row.

Example:

To get data of 'cust_country',SUM of 'opening_amt' for each 'cust_country' and number of 'cust_country' from the 'customer' table with the following condition -

1. data should be a group on 'cust_country',

the following SQL statement can be used :

Sample table: customer

SQL Code:

-- Selecting columns: cust_country, SUM(opening_amt), COUNT(cust_country)
-- From the 'customer' table
SELECT cust_country, SUM(opening_amt), COUNT(cust_country)
-- Grouping the results by the 'cust_country' column
-- Result: Sum of 'opening_amt' and count of occurrences for each 'cust_country'
FROM customer
GROUP BY cust_country;

Explanation:

• SELECT cust_country, SUM(opening_amt), COUNT(cust_country): This is the main part of the SQL query. It selects the 'cust_country' column from the 'customer' table and calculates the sum of 'opening_amt' for each country using the SUM() function. Additionally, it counts the occurrences of each country using the COUNT() function applied to the 'cust_country' column. The result will include three columns: 'cust_country', the sum of 'opening_amt' for each country, and the count of occurrences of each country.


• FROM customer: This specifies the source of the data for the query, which is the 'customer' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'customer' table.


• GROUP BY cust_country: This clause groups the result set by the 'cust_country' column. The GROUP BY clause is used with aggregate functions like SUM() and COUNT() to divide the rows returned from the SELECT statement into groups based on the values in one or more columns. In this case, it groups the rows based on the values in the 'cust_country' column.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

CUST_COUNTRY         SUM(OPENING_AMT) COUNT(CUST_COUNTRY)
-------------------- ---------------- -------------------
USA                             18000                   4
India                           73000                  10
Australia                       19000                   3
Canada                          25000                   3
UK                              26000                   5

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here is a slide presentation of all aggregate functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: COUNT Having and Group by
Next: SUM using GROUP BY