This page contains a list of aggregate functions that are available in SQLite by default.
Continue readingTag: functions
How Group_Concat() Works in SQLite
SQLite has a group_concat() function that allows you to concatenate multiple results returned for a column into one.
This is sometimes referred to as “string aggregation”.
For example, when querying a database, instead of having each column’s value output in a new row, you can use group_concat() to have them output as a comma separated list.
How Substr() Works in SQLite
The SQLite substr() function allows you to return a substring from a string, based on a given starting location within the string.
It requires two arguments, and accepts a third optional argument.
Continue readingHow Round() Works in SQLite
In SQLite, the round() function allows you to round numbers up or down to a given decimal place.
It returns a floating-point value from the first argument, with the number of decimal places that you specify in the (optional) second argument.
If you don’t provide the second argument, it’s assumed to be 0.
Continue readingHow SQLite Lower() Works
The SQLite lower() function allows you to convert a string to lowercase characters.
More precisely, it returns a copy of its argument, with all ASCII characters converted to lowercase.
Continue readingHow SQLite Upper() Works
The SQLite upper() function allows you to convert a string to uppercase characters.
More precisely, it returns a copy of its argument, with all ASCII characters converted to uppercase.
Continue readingHow SQLite Nullif() Works
The SQLite nullif() function is a handy function that allows you to treat certain values as NULL when required.
It’s not to be confused with the ifnull() function, which does the opposite – enables you to treat NULL values as something else.
The nullif() function accepts two arguments, and returns its first argument if the arguments are different and NULL if the arguments are the same.
How SQLite Random() Works
The SQLite random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
A pseudo-random number is a number that appears to be random, but is not truely random. A pseudo-random number is not truely random because its value was generated by a known seed. However, a pseudo-random number will appear to be random if the user has no knowledge of the seed or algorithm that created it.
Therefore, pseudo-random numbers are often considered good enough for many applications.
Continue readingHow SQLite Ifnull() Works
The SQLite ifnull() function allows you to replace NULL values with another value.
It takes two arguments, and it returns a copy of its first non-NULL argument, or NULL if both arguments are NULL.
The ifnull() function is equivalent to coalesce() with two arguments.
How Coalesce() Works in SQLite
In SQLite, the coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL.
It’s similar to the ifnull() function, except that coalesce() accepts more than two arguments (ifnull() only accepts two arguments).