SQLite 3 reference · Aggregate Functions

SQLite AVG() Function

Return the average of the non-NULL values in a group, always as a real number. NULLs are ignored, not counted as 0.

SQLite 3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

avg(X)  |  avg(DISTINCT X)

Parameter	Description
`X`	A column or expression.

A real number, or NULL for no values.

Average per groupTry it yourself

SELECT department, round(avg(salary)) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Output

department | avg_salary
Management | 250000
Engineering | 148400
Sales | 109333
Support | 65000

The share of rows matching a conditionTry it yourself

SELECT avg(city IS NULL) AS share_without_city
FROM customers;

Output

share_without_city
0.1

SUM() · Add up the non-NULL values in a group. Returns NULL when there are no values; total() returns 0.0 instead.
COUNT() · Count rows: count(*) counts every row, count(X) counts rows where X isn't NULL, count(DISTINCT X) counts unique values.
ROUND() · Round a number to a given number of decimal places (0 by default). Halves round away from zero.
SQL Aggregate Functions · tutorial
SQL GROUP BY · tutorial