Database.Guide – Page 335

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.

SQLite Sum() vs Total(): What’s the Difference?

May 28, 2020May 8, 2020 by Ian

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.

How SQLite Total() Works

May 28, 2020May 8, 2020 by Ian

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

May 28, 2020May 8, 2020 by Ian

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.

How SQLite Min() Works

May 28, 2020May 7, 2020 by Ian

The SQLite min() function returns the minimum non-NULL value from all values in a group.

The minimum value is the value that would appear first in a query that uses an ORDER BY clause on the same column.

How SQLite Max() Works

May 28, 2020May 6, 2020 by Ian

The SQLite max() function returns the maximum value from all values in a group.

The maximum value is the value that would appear last in a query that uses an ORDER BY clause on the same column.

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.