SQLite sum() Function

The SQLite sum() function computes the sum of all specified values ​​in a group and returns it.

Syntax

Here is the syntax of the SQLite sum() function:

sum(expr)

Parameters

expr

Required. A column name or expression that computes the sum.

Return value

The SQLite sum() function returns the sum of all specified values ​​in a group.

Examples

To demonstrate the usages of sum(), we simulate a temporary set with the following UNION statement:

SELECT 'Tim' name, 'Math' subject, 8 'mark'
UNION
SELECT 'Tim' name, 'English' subject, 9 'mark'
UNION
SELECT 'Tom' name, 'Math' subject, 7 'mark'
UNION
SELECT 'Tom' name, 'English' subject, 5 'mark';

name  subject  mark
----  -------  ----
Tim   English  9
Tim   Math     8
Tom   English  5
Tom   Math     7

Here, we have some rows for marks of students, and in each row is a student’s mark for one subject.

To get the total marks of each student, use the following statement:

SELECT
    t.name,
    sum(t.mark) 'total marks'
FROM (
    SELECT 'Tim' name, 'Math' subject, 8 'mark'
    UNION
    SELECT 'Tim' name, 'English' subject, 9 'mark'
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 'mark'
    UNION
    SELECT 'Tom' name, 'English' subject, 5 'mark'
) t
GROUP BY t.name;

name  total marks
----  -----------
Tim   17
Tom   12

According the statement, SQLite divides all rows into two groups by name first, and sum marks as total marks in each group.