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 readingIn 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).
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 readingThe 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.
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.
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 readingIf 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.
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 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.
The SQLite strftime() function enables you to return a date and time value in a specified format.
The actual date/time value returned is based on a time string that you provide as an argument when you call the function, as well as any modifiers you include (as optional arguments). A modifier allows you to change the date, such as add a number of days, set it to local time, etc
Continue reading