SQL Functions Cheat Sheet

Aggregate, string, date, and conditional functions with dialect differences.

Updated October 23, 2026

-- result comments show what each expression returns. Where function names differ between databases, a table shows each spelling.

Aggregate functions#

Turn many rows into one value (per group with GROUP BY).

SQLRun

SELECT department,
       COUNT(*)                   AS people,       -- rows
       COUNT(manager_id)          AS with_manager, -- non-NULL values only
       COUNT(DISTINCT city)       AS cities,       -- unique non-NULL values
       SUM(salary)                AS payroll,
       AVG(salary)                AS avg_salary,
       MIN(salary)                AS lowest,
       MAX(salary)                AS highest
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;             -- filter groups (WHERE filters rows)

Rule	Detail
`NULL`s	ignored by every aggregate except `COUNT(*)`
No rows	`COUNT` returns `0`; `SUM`, `AVG`, `MIN`, `MAX` return `NULL`
Grouping	every selected column must be in `GROUP BY` or inside an aggregate

Join text from many rows:

Database	Function
PostgreSQL, SQL Server 2017+	`STRING_AGG(name, ', ')`
MySQL	`GROUP_CONCAT(name SEPARATOR ', ')`
SQLite	`GROUP_CONCAT(name, ', ')`
Oracle	`LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)`

String functions#

SQL

UPPER('asha')                  -- 'ASHA'
LOWER('SQL')                   -- 'sql'
LENGTH('Python')               -- 6
SUBSTR('Python', 1, 3)         -- 'Pyt'      (positions start at 1)
TRIM('  hi  ')                 -- 'hi'       (also LTRIM, RTRIM)
REPLACE('a-b-c', '-', '/')     -- 'a/b/c'
'Asha' || ' ' || 'Rao'         -- 'Asha Rao'

Task	PostgreSQL	MySQL	SQL Server	SQLite
Length	`LENGTH(s)`	`CHAR_LENGTH(s)`	`LEN(s)`	`LENGTH(s)`
Substring	`SUBSTRING(s, 1, 3)`	`SUBSTRING(s, 1, 3)`	`SUBSTRING(s, 1, 3)`	`SUBSTR(s, 1, 3)`
Position of `@`	`POSITION('@' IN s)`	`LOCATE('@', s)`	`CHARINDEX('@', s)`	`INSTR(s, '@')`
Join strings	`a \|\| b`, `CONCAT(a, b)`	`CONCAT(a, b)`	`a + b`, `CONCAT(a, b)`	`a \|\| b`
Split by delimiter	`SPLIT_PART(s, '@', 2)`	`SUBSTRING_INDEX(s, '@', -1)`	`STRING_SPLIT` (table)	`SUBSTR` + `INSTR`

|| with a NULL gives NULL. Wrap optional values in COALESCE(col, '').

Numeric functions#

SQL

ROUND(3.14159, 2)              -- 3.14
ABS(-7)                        -- 7
7 % 3                          -- 1         (MOD(7, 3) in Oracle)
CEIL(4.2)                      -- 5         (CEILING in SQL Server)
FLOOR(4.8)                     -- 4
POWER(2, 10)                   -- 1024
SQRT(16)                       -- 4

Integer division: 7 / 2 is 3 in PostgreSQL, SQL Server and SQLite, but 3.5 in MySQL and Oracle. Write 7 / 2.0 (or CAST) to always get 3.5. In SQLite, CEIL, FLOOR, POWER and SQRT require version 3.35+ built with math functions enabled.

Date functions#

Task	PostgreSQL	MySQL	SQL Server	SQLite
Today	`CURRENT_DATE`	`CURRENT_DATE`	`CAST(GETDATE() AS DATE)`	`date('now')`
Now	`NOW()`	`NOW()`	`GETDATE()`	`datetime('now')`
Year	`EXTRACT(YEAR FROM d)`	`YEAR(d)`	`YEAR(d)`	`strftime('%Y', d)`
Month bucket	`DATE_TRUNC('month', d)`	`DATE_FORMAT(d, '%Y-%m')`	`DATETRUNC(month, d)` (2022+)	`strftime('%Y-%m', d)`
Add 30 days	`d + INTERVAL '30 days'`	`DATE_ADD(d, INTERVAL 30 DAY)`	`DATEADD(day, 30, d)`	`date(d, '+30 days')`
Days between	`d2 - d1`	`DATEDIFF(d2, d1)`	`DATEDIFF(day, d1, d2)`	`julianday(d2) - julianday(d1)`

SQL

-- SQLite examples
date('2026-10-23', '+30 days')                      -- '2026-11-22'
strftime('%Y', '2026-10-23')                        -- '2026'
julianday('2026-12-25') - julianday('2026-10-23')   -- 63.0

Filter a period with a half-open range, which works for dates and timestamps:

SQL

WHERE order_date >= '2026-02-01' AND order_date < '2026-03-01'

Conditional and NULL functions#

SQL

CASE WHEN 82 >= 90 THEN 'A'
     WHEN 82 >= 75 THEN 'B'
     ELSE 'C' END                       -- 'B'

COALESCE(NULL, NULL, 'default')         -- 'default'   (first non-NULL)
NULLIF(5, 5)                            -- NULL        (NULL if equal)
NULLIF(5, 3)                            -- 5
100 / NULLIF(0, 0)                      -- NULL        (avoids division by zero)

Vendor versions of COALESCE with two arguments: IFNULL (MySQL, SQLite), ISNULL (SQL Server), NVL (Oracle).

Conditional aggregation (count or sum only some rows):

SQLRun

SELECT COUNT(*)                                          AS total,
       SUM(CASE WHEN city = 'Mumbai' THEN 1 ELSE 0 END)  AS in_mumbai,
       SUM(CASE WHEN salary > 60000 THEN salary ELSE 0 END) AS high_payroll
FROM employees;

Type conversion#

SQL

CAST('42' AS INTEGER) + 1      -- 43
CAST(price AS DECIMAL(10, 2))
'42'::integer                  -- PostgreSQL shorthand

Window functions#

Calculate across rows without collapsing them. Add PARTITION BY to restart per group.

SQLRun

SELECT student, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, student) AS row_num,
       RANK()       OVER (ORDER BY score DESC)          AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC)          AS dense
FROM scores
WHERE subject = 'Maths';

Output

student | score | row_num | rnk | dense
--------+-------+---------+-----+------
Asha    | 92    | 1       | 1   | 1
Meera   | 92    | 2       | 1   | 1
Priya   | 85    | 3       | 3   | 2
Ravi    | 85    | 4       | 3   | 2
John    | 78    | 5       | 5   | 3

Function	Returns
`ROW_NUMBER()`	1, 2, 3… (no ties)
`RANK()`	ties share a rank, then a gap (1, 1, 3)
`DENSE_RANK()`	ties share a rank, no gap (1, 1, 2)
`LAG(col)` / `LEAD(col)`	value from the previous / next row
`SUM(col) OVER (ORDER BY …)`	running total
`AVG(col) OVER (… ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)`	3-row moving average
`SUM(col) OVER (PARTITION BY g)`	group total on every row

Window functions cannot be used in WHERE. Compute them in a subquery or CTE, then filter.

Full lessons: Aggregate Functions, String Functions, Date Functions, CASE, NULL Values, Window Functions.

Want the full explanations? Go to the Learn SQL course →