
If you write SQL regularly, you'll notice something quickly: a small set of functions does most of the heavy lifting. Knowing them well is the difference between writing ten clumsy lines and one clean query.
This post walks through 15 functions worth having in your toolkit, grouped by what they do. Each one comes with a diagram so you can see what happens to the data, not just read about it.
mindmap
root((15 SQL Functions))
Aggregate
COUNT
SUM
AVG
MIN and MAX
Numeric and NULL
ROUND
COALESCE
NULLIF
CAST
String
CONCAT
SUBSTRING
TRIM / UPPER / LOWER
REPLACE
Date
DATE_TRUNC / EXTRACT
Window
ROW_NUMBER
LAG / LEADThe Sample Data
Every example uses the same two tables, so you can follow along. One customer can place many orders.
erDiagram
CUSTOMERS ||--o{ ORDERS : places
CUSTOMERS {
int customer_id PK
string first_name
string last_name
string email
string phone
string city
}
ORDERS {
int order_id PK
int customer_id FK
date order_date
decimal amount
string status
}A note on dialects: Examples use standard SQL / PostgreSQL syntax. Most functions work almost identically in MySQL, SQL Server, and others, but a few differ slightly. I've flagged those where it matters.
Aggregate Functions
Aggregates collapse many rows into a single value. They're the foundation of reporting and analytics.
flowchart LR
R["Many order rows"] --> G["GROUP BY customer_id"]
G --> S["SUM: lifetime value"]
G --> AV["AVG: average order size"]
G --> M["MIN / MAX: first and latest order"]
G --> C["COUNT: number of orders"]1. COUNT()
Counts rows. The three common forms behave differently:
SELECT
COUNT(*) AS total_rows, -- every row
COUNT(phone) AS rows_with_phone, -- skips NULLs
COUNT(DISTINCT city) AS unique_cities -- unique non-NULL values
FROM customers;Here's what each form returns for a column holding London, London, NULL, Paris:
flowchart LR
D["city column:<br/>London, London, NULL, Paris"] --> A["COUNT(*) = 4<br/>every row"]
D --> B["COUNT(city) = 3<br/>skips the NULL"]
D --> C["COUNT(DISTINCT city) = 2<br/>London, Paris"]Tip: COUNT(*) counts rows; COUNT(column) counts non-NULL values. Mixing them up is one of the most common sources of "why doesn't this number match?" bugs.
2. SUM()
Adds up numeric values.
SELECT customer_id, SUM(amount) AS lifetime_value
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY lifetime_value DESC;3. AVG()
Returns the mean of a column.
SELECT AVG(amount) AS avg_order_value
FROM orders;Gotcha: AVG() ignores NULLs. If 10 rows exist but 2 have a NULL amount, the average is calculated over 8. If you want NULLs treated as zero, wrap the column first (see COALESCE below):
SELECT AVG(COALESCE(amount, 0)) FROM orders;4. MIN() and MAX()
Find the smallest and largest values. They work on numbers, dates, and even text.
SELECT
customer_id,
MIN(order_date) AS first_order,
MAX(order_date) AS most_recent_order
FROM orders
GROUP BY customer_id;This pattern is perfect for questions like "when did each customer first buy from us?"
Numeric and NULL-Handling Functions
Real data is messy. These functions help you tame it.
5. ROUND()
Rounds a number to a given number of decimal places.
SELECT ROUND(AVG(amount), 2) AS avg_order_value
FROM orders;
-- 84.3333333 becomes 84.33A negative second argument rounds to the left of the decimal point: ROUND(1234, -2) returns 1200.
6. COALESCE()
Returns the first non-NULL value from a list. It's the Swiss Army knife for default values.
SELECT
first_name,
COALESCE(phone, email, 'No contact info') AS best_contact
FROM customers;Think of it as a chain of fallbacks:
flowchart TD
A{"phone is NULL?"} -- No --> P["Return phone"]
A -- Yes --> B{"email is NULL?"}
B -- No --> E["Return email"]
B -- Yes --> D["Return 'No contact info'"]Use it any time a NULL would break a calculation or look ugly in a report.
7. NULLIF()
Returns NULL if two values are equal; otherwise returns the first value. It sounds odd, but it's the cleanest way to avoid division-by-zero errors.
SELECT
customer_id,
SUM(amount) / NULLIF(COUNT(order_id), 0) AS avg_per_order
FROM orders
GROUP BY customer_id;If the divisor would be 0, NULLIF turns it into NULL, so the result is NULL instead of an error.
flowchart TD
A["NULLIF(order_count, 0)"] --> B{"order_count = 0?"}
B -- Yes --> C["Returns NULL"]
B -- No --> D["Returns order_count"]
C --> E["SUM / NULL = NULL<br/>no error"]
D --> F["SUM / order_count<br/>normal result"]8. CAST()
Converts a value from one data type to another.
-- Convert a timestamp to a date
SELECT CAST(order_date AS DATE) FROM orders;
-- Avoid integer division
SELECT CAST(completed_orders AS DECIMAL(10,2)) / total_orders AS completion_rate
FROM order_summary;Why it matters: In many databases, dividing two integers returns an integer. Casting one side to a decimal fixes that. Postgres also supports the shorthand amount::numeric.
flowchart LR
A["3 / 2"] --> B["Integer division<br/>result: 1"]
C["CAST(3 AS DECIMAL) / 2"] --> D["Decimal division<br/>result: 1.5"]String Functions
Text columns are rarely clean. These four handle most cleanup jobs.
9. CONCAT()
Joins strings together.
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;Many databases also support the || operator (first_name || ' ' || last_name). One difference worth knowing: in some systems CONCAT treats NULLs as empty strings, while || returns NULL if any part is NULL.
10. SUBSTRING()
Extracts part of a string by position and length.
SELECT SUBSTRING(phone, 1, 3) AS area_code
FROM customers;In MySQL and Oracle you'll often see SUBSTR() instead; the arguments are the same.
11. TRIM(), UPPER(), and LOWER()
These three are best used together to normalize text before comparing or grouping.
SELECT LOWER(TRIM(email)) AS clean_email
FROM customers;TRIM()removes leading and trailing whitespace.LOWER()/UPPER()standardize case.
flowchart LR
A["' [email protected] '"] -->|TRIM| B["'[email protected]'"]
B -->|LOWER| C["'[email protected]'"]Without this step, '[email protected] ' and '[email protected]' look like two different customers.
12. REPLACE()
Swaps every occurrence of a substring with another.
SELECT REPLACE(phone, '-', '') AS digits_only
FROM customers;
-- '555-123-4567' becomes '5551234567'Handy for stripping punctuation, fixing typos, or standardizing formats. Here are CONCAT, SUBSTRING, and REPLACE side by side:
flowchart LR
P["'555-123-4567'"] -->|"SUBSTRING(phone, 1, 3)"| A["'555'"]
P -->|"REPLACE(phone, '-', '')"| B["'5551234567'"]
F["'Anna' and 'Lee'"] -->|"CONCAT(first, ' ', last)"| N["'Anna Lee'"]Date Functions
13. DATE_TRUNC() and EXTRACT()
Time-based analysis is everywhere: monthly revenue, weekly signups, yearly trends. These two functions make it easy.
DATE_TRUNC snaps a date down to the start of a period:
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;flowchart LR
subgraph Raw["order_date"]
A["2026-01-03"]
B["2026-01-17"]
C["2026-02-05"]
end
subgraph Truncated["DATE_TRUNC month"]
M1["2026-01-01"]
M2["2026-02-01"]
end
subgraph Grouped["GROUP BY + SUM"]
R1["January revenue"]
R2["February revenue"]
end
A --> M1
B --> M1
C --> M2
M1 --> R1
M2 --> R2EXTRACT pulls out a single component such as the year, month, or day of week:
SELECT EXTRACT(YEAR FROM order_date) AS order_year, COUNT(*)
FROM orders
GROUP BY order_year;Dialect note: DATE_TRUNC is native to PostgreSQL, Snowflake, and BigQuery. In MySQL, use DATE_FORMAT(); in SQL Server, use DATETRUNC() (2022+) or DATEPART().
Window Functions
Window functions perform calculations across related rows without collapsing them into one row the way GROUP BY does. They look intimidating at first, but they unlock queries that are painful or impossible otherwise.
14. ROW_NUMBER()
Assigns a sequential number to rows within a group. The classic use case is "get the latest row per group."
SELECT *
FROM (
SELECT
order_id,
customer_id,
order_date,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders
) ranked
WHERE rn = 1;PARTITION BY defines the groups, and ORDER BY decides the numbering within each one. Keeping rn = 1 leaves each customer's most recent order:
flowchart LR
subgraph C1["customer 1, newest first"]
A1["March order: rn = 1"]
A2["February order: rn = 2"]
A3["January order: rn = 3"]
end
subgraph C2["customer 2, newest first"]
B1["April order: rn = 1"]
B2["February order: rn = 2"]
end
A1 --> R["WHERE rn = 1<br/>latest order per customer"]
B1 --> RRelated functions worth exploring: RANK() and DENSE_RANK(), which handle ties differently.
15. LAG() and LEAD()
Access a value from the previous row (LAG) or the next row (LEAD). They're ideal for period-over-period comparisons.
WITH monthly AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
1
) AS pct_change
FROM monthly;Each row simply "looks back" at the row before it:
flowchart LR
J["Jan<br/>revenue: 1000<br/>prev: NULL"]
F["Feb<br/>revenue: 1200<br/>prev: 1000<br/>change: +20%"]
M["Mar<br/>revenue: 1080<br/>prev: 1200<br/>change: -10%"]
J --> F --> M
F -.->|LAG looks back| J
M -.->|LAG looks back| FNotice how this query pulls together DATE_TRUNC, SUM, LAG, NULLIF, and ROUND. That's the real payoff of knowing these functions: they compose.
flowchart LR
O["orders"] --> T["DATE_TRUNC<br/>group by month"]
T --> S["SUM<br/>monthly revenue"]
S --> L["LAG<br/>previous month"]
L --> N["NULLIF<br/>avoid divide by zero"]
N --> R["ROUND<br/>percent change"]Which Function Do I Need?
When you're stuck, start from the problem you're trying to solve:
flowchart TD
Q{"What do you need to do?"}
Q --> A["Summarize many rows"]
Q --> B["Handle NULLs or data types"]
Q --> C["Clean or build text"]
Q --> D["Work with dates"]
Q --> E["Compare rows to each other"]
A --> A1["COUNT, SUM, AVG, MIN, MAX"]
B --> B1["COALESCE, NULLIF, CAST, ROUND"]
C --> C1["CONCAT, SUBSTRING, TRIM, UPPER, LOWER, REPLACE"]
D --> D1["DATE_TRUNC, EXTRACT"]
E --> E1["ROW_NUMBER, LAG, LEAD"]Quick Reference
# | Function | What it does |
|---|---|---|
1 |
| Counts rows or non-NULL values |
2 |
| Totals numeric values |
3 |
| Calculates the mean (ignores NULLs) |
4 |
| Finds smallest / largest value |
5 |
| Rounds to N decimal places |
6 |
| Returns first non-NULL value |
7 |
| Returns NULL when two values match |
8 |
| Converts data types |
9 |
| Joins strings |
10 |
| Extracts part of a string |
11 |
| Cleans and normalizes text |
12 |
| Substitutes text |
13 |
| Buckets or splits dates |
14 |
| Numbers rows within groups |
15 |
| Reads previous / next row values |
Final Thoughts
You don't need to memorize every function in your database's documentation. Master these 15 and you'll be able to handle the vast majority of day-to-day analysis, reporting, and data-cleaning tasks.
A good way to build fluency is to take a table you already know and answer a few questions with each function. Try calculating a month-over-month growth rate, finding each customer's first purchase, or cleaning up a messy text column. Once these become second nature, you'll spend less time fighting syntax and more time finding answers in your data.
Which SQL function saved you the most time? Share it in the comments.
Comments(0)
Join the discussion by logging into your account.
No comments yet. Be the first to comment!