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
| DBMS | Command |
| MySQL | Supported |
| PostgreSQL | Supported |
| SQL Server | Supported |
| Oracle | Supported |
DB2 and Oracle Syntax :
SUM ([ALL | DISTINCT] expression ) OVER (window_clause)
Parameters:
| Name | Description |
|---|---|
| ALL | Applies to all values. |
| DISTINCT | Return the SUM of unique values. |
| expression | Expression made up of a single constant, variable, scalar function, or column name. The expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted. |
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
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
...........
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
Visual 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
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | ........
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
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
...........
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
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | ........
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:
SQL Code:
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