SELECT tablea.column1, tablea.column2, tableb.column3 AS somealias, SUM(tableb.column4)
FROM tablea
LEFT JOIN tableb
ON tablea.columnx = tableb.columnx
WHERE (tablea.column1 = 'someString') AND (tablea.column2 = someNumber)
GROUP BY tablea.column1, tablea.column2, somealias
HAVING (somealias = 'someValue')
ORDER BY SUM(tableb.column4) DESC
LIMIT 100;
The SQL standard requires double-quotes around identifiers and single-quotes around string literals.
- "this is a SQL column/table name"
- 'this is a SQL text value'
When filtering data according to text values, it is strongly recommended to use single quotations. For example:
SELECT name
FROM pets
WHERE pet_type = 'dog' OR pet_type = 'cat';
When filtering on these expressions, use the keyword HAVING.
AVG(fieldname)
Returns the mean value of a numeric field
COUNT(*) or COUNT(fieldname)
Returns the count of records. If using the wildcard *, returns count of all rows in that table. If specifying a particular field, returns count of all records with a non-null value for that field.
MAX(fieldname)
Returns the maximum value in a numeric field
MIN(fieldname)
Returns the minimum value in a numeric field
SUM(n)
Returns the sum of numeric values in the field n
When filtering on these expressions, use the keyword WHERE.
While many of these functions are used across different database management systems, some are unique to SQLite as indicated.
'string1' || 'string2'
field1 || 'someString' || field2
Returns the concatenation of multiple text fields and/or character strings
ABS(n)
Returns the absolute (positive) value of the numeric expression n
IFNULL(x, y)
Unique to SQLite
Returns x if it is non-NULL, otherwise returns y
LENGTH(s)
Returns the length of the string expression s
LOWER(s)
Returns the string expression s converted to lowercase
RANDOM()
Unique to SQLite
Returns a random integer between -9223372036854775808 and +9223372036854775807.
REPLACE(s, f, r)
Unique to SQLite
Returns the string expression s in which every occurrence of f has been replaced with r
ROUND(n) or ROUND(n, x)
Returns the numeric expression n rounded to x digits after the decimal point (0 by default)
SUBSTR(s, x, y) or SUBSTR(s, x)
Unique to SQLite
Returns the portion of the string expression s starting at the character position x (leftmost position is 1), y characters long (or to the end of s if y is omitted)
TRIM(s)
Returns the string expression s without leading and trailing whitespace characters
UPPER(s)
Returns the string expression s converted to uppercase