how to – Page 172

List all Indexes in an SQLite Database

August 5, 2024May 13, 2020 by Ian

In this article I outline two ways to return a list of indexes in an SQLite database.

The first (and most obvious) method is to use the .indexes dot command. The second method is to query the sql_master table.

Tweak your Avg() Results in SQLite with the DISTINCT Keyword

May 28, 2020May 11, 2020 by Ian

If you know about the avg() function in SQLite, you’re probably aware that it returns the average of all non-NULL X within a group.

But did you know you can add the DISTINCT keyword to this function?

If you add the DISTINCT keyword, avg() will calculate its results based on distinct values only. This is essentially the same as removing duplicate values and then calculating the average on the remaining values.

How to Add Just the Distinct Values with SQLite Sum()

May 28, 2020May 10, 2020 by Ian

In SQLite, the Sum() function accepts an optional DISTINCT keyword that enables you to add just the distinct values in the group. That is, it removes any duplicates from its calculation.

So if there are say, three rows that contain 10, only one of those rows will be included in the results.

Remove Duplicates from Count() Results in SQLite

May 28, 2020May 10, 2020 by Ian

When using the count() function in SQLite, you might find yourself in the situation where you only want to count distinct values. That is, you don’t want duplicate values to be counted multiple times.

List all Temporary Tables in SQLite

November 15, 2025May 10, 2020 by Ian

As with most things in SQLite, there’s more than one way to get a list of temporary tables in a database.

Here I present two ways to return temporary tables in SQLite.

2 Ways to List the Tables in an SQLite Database

November 15, 2025May 9, 2020 by Ian

Here are two ways to return a list of tables in all attached databases in SQLite.

The first method returns all tables and views for all attached databases.

The second method gives you the option of returning both tables and views, or just tables, but only for the primary database.

Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article.

How SQLite Avg() Works

May 28, 2020May 6, 2020 by Ian

The SQLite avg() function returns the average value of all non-NULL values within a group.

It accepts one argument, which is the value or group of values.

Combine SQLite Count() with GROUP BY to Add a “Count” Column to your Result Set

May 28, 2020May 6, 2020 by Ian

If you need to add a “count” column to the result set of a database query when using SQLite, you can use the count() function to provide the count, and the GROUP BY clause to specify the column for which to group the results.

How SQLite Count() Works

May 28, 2020May 6, 2020 by Ian

The SQLite count() function can be used to return the number of rows in a result set.

It can also be used to return the number of times a given column is not NULL in the result set.

It can be used in two ways. If you pass in the asterisk (*) wildcard character, it will return the total number of rows in the group. If you provide the name of a column, it will return the number of times that column is not NULL.

SQLite Date & Time Functions

January 28, 2021May 5, 2020 by Ian

SQLite supports five functions for working with dates and times. These are:

All of these functions accept a time string as an argument. They also accept other arguments for modifying/formatting the output.

The strftime() function is the most versatile function of the five. In fact, this function can do everything the other four can.

In other words, you could use strftime() for all your date and time formatting requirements. The other four functions are available solely for convenience.