SQLite | SQLite Functions | Calculate an Average with avg
The avg() function calculates the average of values in a column. This article explains how to use it.
Using the avg Function
Use avg() to calculate an average. The syntax is as follows.
avg(column_name)
It averages non-NULL values in the specified column. If every value is NULL, it returns NULL. With GROUP BY, it calculates an average for each group.
A string that cannot be converted to a number is treated as 0.
–
Create the following table for an example.
create table user (name text, point integer, gender text);
sqlite> create table user (name text, point integer, gender text);
sqlite>
Insert the following data.
insert into user values ('dekuma', 72, 'Man');
insert into user values ('kimkc', 94, 'Woman');
insert into user values ('araikuma', 58, 'Woman');
insert into user values ('happykuma', 82, 'Man');
sqlite> insert into user values ('dekuma', 72, 'Man');
sqlite> insert into user values ('kimkc', 94, 'Woman');
sqlite> insert into user values ('araikuma', 58, 'Woman');
sqlite> insert into user values ('happykuma', 82, 'Man');
sqlite>
Use avg() to calculate the average of point.
select avg(point) from user;
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select avg(point) from user;
avg(point)
----------
76.5
sqlite>
The average is (72 + 94 + 58 + 82) ÷ 4 = 76.5.
Next, group by gender and calculate each average.
select gender, avg(point) from user group by gender;
sqlite> select gender, avg(point) from user group by gender;
gender avg(point)
---------- ----------
Man 77.0
Woman 76.0
sqlite>
With GROUP BY, avg() operates per group. The result is 77 for man and 76 for woman.
NULL and String Values in the Averaged Column
NULL values are excluded from the calculation. Add the following row.
insert into user values ('raccoon', NULL, 'Man');
sqlite> insert into user values ('raccoon', NULL, 'Man');
sqlite>
sqlite> select * from user;
name point gender
---------- ---------- ----------
dekuma 72 Man
kimkc 94 Woman
araikuma 58 Woman
happykuma 82 Man
raccoon Man
sqlite>
Calculate the point average again.
select avg(point) from user;
sqlite> select avg(point) from user;
avg(point)
----------
76.5
sqlite>
Because the NULL value is ignored, the average remains 76.5.
Next, add a string that cannot be converted to a number; SQLite treats it as 0.
insert into user values ('mykuma', 'NODATA', 'Woman');
sqlite> insert into user values ('mykuma', 'NODATA', 'Woman');
sqlite>
sqlite> select * from user;
name point gender
---------- ---------- ----------
dekuma 72 Man
kimkc 94 Woman
araikuma 58 Woman
happykuma 82 Man
raccoon Man
mykuma NODATA Woman
sqlite>
Use avg() to calculate the average of point.
select avg(point) from user;
sqlite> select avg(point) from user;
avg(point)
----------
61.2
sqlite>
The string counts as 0, so the average is (72 + 94 + 58 + 82 + 0) ÷ 5 = 61.2. It is included as zero rather than excluded.