SQLite TOTAL() Function

The SQLite TOTAL() 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 TOTAL() returns 0.0.

The result of TOTAL() is always a floating point value.

This function never throws an "integer overflow" exception.

Note: See also SUM() function.

Syntax

The syntax for using TOTAL() function is given below:

SELECT TOTAL(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 TOTAL() function. See the syntax below:

SELECT column1, column2, ...
       TOTAL(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Parameters

Return Value

Returns the summed value of a given expression.

Example - With Single Column

Consider a database table called Employee with the following records:

To get the combined total salary of all employees whose age is greater than 25, the following query can be used:

SELECT TOTAL(Salary) AS TotalSalary 
FROM Employee
WHERE Age > 25;

This will produce the result as shown below:

Example - Using DISTINCT

The DISTICT clause can be used with TOTAL() function. For example - To get the combined total salary of distinct (unique) salaries, the following code can be used:

SELECT TOTAL(DISTINCT Salary) AS TotalUniqueSalary 
FROM Employee;

This will produce the result as shown below:

Example - Using Formula

The expression contained within the TOTAL() 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 TOTAL(Salary * 0.15) AS TotalBonusAmount
FROM Employee;

This will produce the result as shown below:

Example - Using GROUP BY

To get the combined total salary of employees group by their age, the following query can be used:

SELECT Age, TOTAL(Salary) AS TotalSalaryByAge 
FROM Employee
GROUP BY Age;

This result of the above code will be: