SQLite Aggregate Functions

Summary: in this tutorial, you will learn about the SQLite aggregate functions to find the maximum, minimum, average, sum, and count of a set of values.

Overview of SQLite aggregate functions

Aggregate functions operate on a set of rows and return a single result. Aggregate functions are often used in conjunction with GROUP BY and HAVING clauses in the SELECT statement.

SQLite provides the following aggregate functions:

• AVG() – returns the average value of a group.
• COUNT() – returns the number of rows that match a specified condition
• MAX() – returns the maximum value in a group.
• MIN() – returns the minimum value in a group
• SUM() – returns the sum of values
• GROUP_CONCAT(expression, separator) – returns a string that is the concatenation of all non-NULL values of the input expression separated by the separator.

SQLite aggregate function syntax

The following shows the syntax of calling an aggregate function except for the GROUP_CONCAT() function:

aggregate_function (DISTINCT | ALL expression)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the aggregate function such as AVG, SUM, or COUNT.
• Second, specify the expression to which the aggregate function applies.

DISTINCT instructs the aggregate function to consider only unique values in the calculation while ALL allows the aggregate function to take all values including duplicates in its calculation.

The following picture illustrates the SUM() aggregate function: