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:

SQLite aggregate function examples

We will use the tracks table from the sample database for the demonstration:

SQLite AVG() function example

The following statement finds the average length of all tracks for each album:

SELECT
    AlbumId,
    ROUND(AVG(Milliseconds) / 60000 ,0) "Average In Minutes"
FROM
    Tracks
GROUP BY
    AlbumId;     
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

• First, the GROUP BY clause divides the tracks by album id into groups.
• Then, the AVG() function applies to each group that has the same album id to calculate the average length of tracks.

SQLite COUNT() function example

The following statement returns the number of rows from the tracks table:

SELECT
    COUNT(*)
FROM
    tracks;
Code language: SQL (Structured Query Language) (sql)

To find the albums and their corresponding track count, you use the following statement:

SELECT
    AlbumId,
    COUNT(TrackId) track_count
FROM
    tracks
GROUP BY
    AlbumId
ORDER BY
    track_count DESC;Code language: SQL (Structured Query Language) (sql)

SQLite SUM() function example

The following example uses the SUM() function to calculate the length of each album in minutes:

SELECT
    AlbumId, 
    SUM(Milliseconds) / 60000 Minutes
FROM
    tracks
GROUP BY
    AlbumId;   
Code language: SQL (Structured Query Language) (sql)

SQLite MAX() function example

To find the longest time of all tracks, you use the MAX() function as follows:

SELECT
    MAX(Milliseconds) / 60000 Minutes
FROM
    tracks;
Code language: SQL (Structured Query Language) (sql)

In order to find the tracks whose length are the longest, you use the subquery:

SELECT
    TrackId,
    Name,
    Milliseconds
FROM
    tracks
WHERE 
    Milliseconds =   (
        SELECT
            MAX(Milliseconds)
        FROM
            tracks);
Code language: SQL (Structured Query Language) (sql)

In this example, the outer query returns the track whose length is equal to the longest time of all tracks returned by the subquery.

SQLite MIN() function example

Similarly, the following statement finds the track whose length is shortest by using the MIN() function:

SELECT
    TrackId,
    Name,
    Milliseconds
FROM
    tracks
WHERE 
    Milliseconds = (
        SELECT
            MIN(Milliseconds)
        FROM
            tracks);
Code language: SQL (Structured Query Language) (sql)

SQLite GROUP_CONCAT() function example

The following statement uses the GROUP_CONCAT() function to return a comma-separated list of track name of the album id 10:

SELECT
    GROUP_CONCAT(name)
FROM
    tracks
WHERE   
    AlbumId = 10; 
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about the SQLite aggregate functions and how to apply them to calculate aggregates.