SQLite SUM() Function
The SQLite SUM() function returns the summed value of an expression where the value of expression is not NULL.
If there are no non-NULL input rows then SUM() returns NULL.
The result of SUM() is an integer value if all non-NULL inputs are integers.
If any input to SUM() is neither an integer nor a NULL, then it returns a floating point value which is an approximation of the mathematical sum.
This function throws an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation.
Note: See also TOTAL() function.
Syntax
The syntax for using SUM() function is given below:
SELECT SUM(column_name) FROM table_name WHERE condition(s);
The SQLite GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the SUM() function. See the syntax below:
SELECT column1, column2, ... SUM(column_name) FROM table_name WHERE condition(s) GROUP BY column1, column2, ...;
Parameters
column1, column2, ... |
Specify the column names that are not encapsulated within the SUM() function. It must be included in the GROUP BY clause. |
column_name |
Specify the column or expression whose summed value need to be returned. |
table_name |
Specify the table name from where the records need to retrieved. |
WHERE condition(s) |
Optional. Specify the condition(s). Records are selected based upon specified condition(s). |
Return Value
Returns the summed value of a given expression.
Example - With Single Column
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
To get the combined total salary of all employees whose age is greater than 25, the following query can be used:
SELECT SUM(Salary) AS TotalSalary FROM Employee WHERE Age > 25;
This will produce the result as shown below:
TotalSalary |
---|
11700 |
Example - Using DISTINCT
The DISTICT clause can be used with SUM() function. For example - To get the combined total salary of distinct (unique) salaries, the following code can be used:
SELECT SUM(DISTINCT Salary) AS TotalUniqueSalary FROM Employee;
This will produce the result as shown below:
TotalUniqueSalary |
---|
11650 |
Example - Using Formula
The expression contained within the SUM() function does not need to be a single field. A formula can also be used with this function. For example - Consider a bonus (15% of Salary) is given to each employee, and to calculate the total bonus amount, the following query can be used:
SELECT SUM(Salary * 0.15) AS TotalBonusAmount FROM Employee;
This will produce the result as shown below:
TotalBonusAmount |
---|
2617.5 |
Example - Using GROUP BY
To get the combined total salary of employees group by their age, the following query can be used:
SELECT Age, SUM(Salary) AS TotalSalaryByAge FROM Employee GROUP BY Age;
This result of the above code will be:
Age | TotalSalaryByAge |
---|---|
24 | 2750 |
25 | 3000 |
27 | 2800 |
28 | 5800 |
30 | 3100 |
❮ SQLite Functions