SQLite avg() Function

The SQLite avg() function calculates the average of all specified values ​​in a group and returns it.

Syntax

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

avg(expr)

Parameters

expr

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

Return value

The SQLite avg() function returns to calculate the average of all specified values ​​in a group.

Examples

To demonstrate the usages of avg(), 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 calculate the average mark for each student, use the following statement:

SELECT
    t.name,
    avg(t.mark) 'average 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  average marks
----  -------------
Tim   8.5
Tom   6.0

According the statement, SQLite divides all rows into two groups by name first, and calculate the average value of all marks in each group.