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.
How AUTOINCREMENT Works in SQLite
In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that’s inserted into the table.
There are a couple of ways you can create an AUTOINCREMENT column:
- You can create it implicitly when you define the column as
INTEGER PRIMARY KEY. - You can create it explicitly with the
AUTOINCREMENTkeyword. One downside of this method is that it uses extra CPU, memory, disk space, and disk I/O overhead.
Both methods cause the column to use an incrementing value each time a new row is inserted with NULL in that column.
However, there are some subtle differences between how each method works.
Continue readingTweak 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 readingSQLite Sum() vs Total(): What’s the Difference?
SQLite has a function called sum() and another function called total(). Both functions do pretty much the same thing – they return the sum of all non-NULL values in a group.
But there is a subtle difference between these two functions.
The difference is in the way they handle NULL inputs.
Continue readingHow SQLite Total() Works
In SQLite, the total() function returns the sum of all non-NULL values in a group.
If there are no non-NULL values, then it returns 0.0.
This function is similar to the sum() function, except in the way it handles NULL inputs. When there are no non-NULL values, then sum() returns NULL (instead of 0.0 like the total() function returns).
How SQLite Sum() Works
The SQLite sum() function returns the sum of all non-NULL values in a group.
If there are no non-NULL values, then it returns NULL.
This function basically enables you to add up all the values in a result set or a table.
Continue reading