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.
Tag: how to
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.
How to Add Just the Distinct Values with SQLite Sum()
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.
Continue readingRemove Duplicates from Count() Results in SQLite
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
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.
Continue reading2 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 readingHow SQLite Avg() Works
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.
Continue readingCombine SQLite Count() with GROUP BY to Add a “Count” Column to your Result Set
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
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
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.