SQLite Group By
Summary: in this tutorial, you will learn how to use SQLite GROUP BY
clause to make a set of summary rows from a set of rows.
Introduction to SQLite GROUP BY
clause
The GROUP BY
clause is an optional clause of the SELECT
statement. The GROUP BY
clause a selected group of rows into summary rows by values of one or more columns.
The GROUP BY
clause returns one row for each group. For each group, you can apply an aggregate function such as MIN
, MAX
, SUM
, COUNT
, or AVG
to provide more information about each group.
The following statement illustrates the syntax of the SQLite GROUP BY
clause.
SELECT
column_1,
aggregate_function(column_2)
FROM
table
GROUP BY
column_1,
column_2;
The GROUP BY
clause comes after the FROM
clause of the SELECT
statement. In case a statement contains a WHERE
clause, the GROUP BY
clause must come after the WHERE
clause.
Following the GROUP BY
clause is a column or a list of comma-separated columns used to specify the group.
SQLite GROUP BY
examples
We use the tracks
table from the sample database for the demonstration.
SQLite GROUP BY
clause with COUNT
function
The following statement returns the album id and the number of tracks per album. It uses the GROUP BY
clause to groups tracks by album and applies the COUNT()
function to each group.
SELECT
albumid,
COUNT(trackid)
FROM
tracks
GROUP BY
albumid;
You can use the ORDER BY
clause to sort the groups as follows:
SELECT
albumid,
COUNT(trackid)
FROM
tracks
GROUP BY
albumid
ORDER BY COUNT(trackid) DESC;
SQLite GROUP BY
and INNER JOIN
clause
You can query data from multiple tables using the INNER JOIN
clause, then use the GROUP BY
clause to group rows into a set of summary rows.
For example, the following statement joins the tracks
table with the albums
table to get the album’s titles and uses the GROUP BY
clause with the COUNT
function to get the number of tracks per album.
SELECT
tracks.albumid,
title,
COUNT(trackid)
FROM
tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
tracks.albumid;
SQLite GROUP BY
with HAVING
clause
To filter groups, you use the GROUP BY
with HAVING
clause. For example, to get the albums that have more than 15 tracks, you use the following statement:
SELECT
tracks.albumid,
title,
COUNT(trackid)
FROM
tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
tracks.albumid
HAVING COUNT(trackid) > 15;
SQLite GROUP BY
clause with SUM
function example
You can use the SUM
function to calculate total per group. For example, to get total length and bytes for each album, you use the SUM
function to calculate total milliseconds and bytes.
SELECT
albumid,
SUM(milliseconds) length,
SUM(bytes) size
FROM
tracks
GROUP BY
albumid;
SQLite GROUP BY
with MAX
, MIN
, and AVG
functions
The following statement returns the album id, album title, maximum length, minimum length, and the average length of tracks in the tracks
table.
SELECT
tracks.albumid,
title,
min(milliseconds),
max(milliseconds),
round(avg(milliseconds),2)
FROM
tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
tracks.albumid;
SQLite GROUP BY
multiple columns example
In the previous example, we have used one column in the GROUP BY
clause. SQLite allows you to group rows by multiple columns.
For example, to group tracks by media type and genre, you use the following statement:
SELECT
MediaTypeId,
GenreId,
COUNT(TrackId)
FROM
tracks
GROUP BY
MediaTypeId,
GenreId;
SQLite uses the combination of values of MediaTypeId
and GenreId
columns as a group e.g., (1,1) and (1,2). It then applies the COUNT
function to return the number of tracks in each group.
SQLite GROUP BY
date example
See the following invoices table from the sample database:
The following statement returns the number of invoice by years.
SELECT
STRFTIME('%Y', InvoiceDate) InvoiceYear,
COUNT(InvoiceId) InvoiceCount
FROM
invoices
GROUP BY
STRFTIME('%Y', InvoiceDate)
ORDER BY
InvoiceYear;
Here is the output:
In this example:
- The function
STRFTIME('%Y', InvoiceDate)
returns a year from a date string. - The
GROUP BY
clause groups the invoices by years. - The function
COUNT()
returns the number of invoice in each year (or group).
In this tutorial, you have learned how to use the SQLite GROUP BY
clause to group rows into a set of summary rows.