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

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.

Continue reading

2 Ways to List the Tables in an SQLite Database

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.

Continue reading