SQL SUM Function
The SQL SUM is an aggregate function used to return the sum of a numeric column.
Syntax
The basic syntax for the SUM function is as follows:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Or the syntax for the SUM function when used with the GROUP BY clause:
SELECT colunm1_name, column2_name, ... columnN_name, SUM(column_name)
FROM table_name
WHERE condition
GROUP BY column1_name, column2_name, ... columnN_name
Parameters:
colunm1_name, column2_name, ... columnN_name: Columns that are not encapsulated within the SUM function and must be included in theGROUP BYclause.column_name: This is the column that will be summed.
Demo Table
To understand more the SUM function, let us suppose that we have a table called "Employee" with the following records:
| emp_no | first_name | last_name | birth_date | hire_date | salary | dept_name |
|---|---|---|---|---|---|---|
| 1001 | James | Smith | 1991-10-23 | 2010-05-13 | 6600 | Customer Service |
| 1002 | Maria | Martinez | 1993-06-13 | 2018-10-08 | 9000 | Development |
| 1003 | Andrew | Rodriguez | 1994-10-25 | 2019-01-23 | 9000 | Finance |
| 1004 | Robert | Hernandez | 1995-09-07 | 2019-09-08 | 9600 | Development |
| 1005 | Donna | Williams | 1996-03-15 | 2012-06-06 | 7400 | Finance |
| 1006 | James | Johnson | 2001-04-30 | 2014-02-18 | 8000 | Marketing |
| 1007 | Stephanie | Miller | 1998-01-17 | 2009-12-12 | 8000 | Human Resources |
| 1008 | Nelson | Lopez | 1999-05-26 | 2010-10-06 | 7300 | Marketing |
SUM Function With Single Condition - Example
The following SQL statement will calculate the combined total salary of all employees whose salary is above $7,500 / month.
SELECT SUM(salary) AS "Total Salary"
FROM employee
WHERE salary > 7500;
The output will be as follows:
+--------------+ | Total Salary | +--------------+ | 43600 | +--------------+
AVG With DISTINCT Clause - Example
You can also use the DISTINCT clause with the SUM function.
The following SQL statement will return the total combined salary of unique salary values where the salary is above $7,500 / month.
SELECT SUM(DISTINCT salary) AS "Total Salary"
FROM employee
WHERE salary > 7500;
The output of the above SQL statement will be as follows:
+--------------+ | Total Salary | +--------------+ | 26600 | +--------------+
SUM With a Formula - Example
The expression encapsulated within the SUM function can be a formula.
The following SQL statement will calculate the combined total salary by Euro. So will do a conversion from Dollar to Euro, we suppose that 1 USD = 0.84 EUR.
SELECT SUM(salary * 0.84) AS "Total Salary in EUR"
FROM employee;
After the execution, the output will look as follows:
+---------------------+ | Total Salary in EUR | +---------------------+ | 54516 | +---------------------+
SUM With GROUP BY Clause - Example
In real-life situations you will need to use the SUM function with the GROUP BY clause. This happens when you have columns listed in the SELECT statement that are not encapsulated in the SUM function.
The following SQL statement will return the department's name and the total salary associated with the department.
SELECT dept_name AS "Department", SUM(salary) AS "Total Salary"
FROM employee
GROUP BY dept_name;
After running the above SQL statement, the result set will look as follows:
+------------------+--------------+ | Department | Total Salary | +------------------+--------------+ | Customer Service | 6600 | | Development | 18600 | | Finance | 16400 | | Human Resources | 8000 | | Marketing | 15300 | +------------------+--------------+
Note: Every column listed in the SELECT statement and not encapsulated in the SUM function must be listed in the GROUP BY clause.