--- title: "15 SQL Functions You Should Know" author: "Sanju Singh" published: "2026-11-18T04:25:58.787Z" updated: "2026-11-18T04:25:58.787Z" url: "https://zyvop.com/15-sql-functions-you-should-know-x9sa9" canonical: "https://zyvop.com/15-sql-functions-you-should-know-x9sa9" tags: ["Window Functions","SQL Functions","SQL","data-analysis","databases"] --- # 15 SQL Functions You Should Know Master the aggregate, string, date, and window functions behind most real-world queries, with simple diagrams that show what each one does. ![Cover image]() 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. ```mermaid 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 / LEAD ``` ## The Sample Data Every example uses the same two tables, so you can follow along. One customer can place many orders. ```mermaid 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. ```mermaid 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: ```sql 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`: ```mermaid flowchart LR D["city column:
London, London, NULL, Paris"] --> A["COUNT(*) = 4
every row"] D --> B["COUNT(city) = 3
skips the NULL"] D --> C["COUNT(DISTINCT city) = 2
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. ```sql 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. ```sql 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): ```sql 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. ```sql 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. ```sql SELECT ROUND(AVG(amount), 2) AS avg_order_value FROM orders; -- 84.3333333 becomes 84.33 ``` A 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. ```sql SELECT first_name, COALESCE(phone, email, 'No contact info') AS best_contact FROM customers; ``` Think of it as a chain of fallbacks: ```mermaid 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. ```sql 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. ```mermaid 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
no error"] D --> F["SUM / order_count
normal result"] ``` ### 8\. `CAST()` Converts a value from one data type to another. ```sql -- 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`. ```mermaid flowchart LR A["3 / 2"] --> B["Integer division
result: 1"] C["CAST(3 AS DECIMAL) / 2"] --> D["Decimal division
result: 1.5"] ``` * * * ## String Functions Text columns are rarely clean. These four handle most cleanup jobs. ### 9\. `CONCAT()` Joins strings together. ```sql 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. ```sql 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. ```sql SELECT LOWER(TRIM(email)) AS clean_email FROM customers; ``` - `TRIM()` removes leading and trailing whitespace. - `LOWER()` / `UPPER()` standardize case. ```mermaid flowchart LR A["' Anna@Example.com '"] -->|TRIM| B["'Anna@Example.com'"] B -->|LOWER| C["'anna@example.com'"] ``` Without this step, `'Anna@Example.com '` and `'anna@example.com'` look like two different customers. ### 12\. `REPLACE()` Swaps every occurrence of a substring with another. ```sql 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: ```mermaid 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: ```sql 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; ``` ```mermaid 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 --> R2 ``` `EXTRACT` pulls out a single component such as the year, month, or day of week: ```sql 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." ```sql 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: ```mermaid 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
latest order per customer"] B1 --> R ``` Related 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. ```sql 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: ```mermaid flowchart LR J["Jan
revenue: 1000
prev: NULL"] F["Feb
revenue: 1200
prev: 1000
change: +20%"] M["Mar
revenue: 1080
prev: 1200
change: -10%"] J --> F --> M F -.->|LAG looks back| J M -.->|LAG looks back| F ``` Notice how this query pulls together `DATE_TRUNC`, `SUM`, `LAG`, `NULLIF`, and `ROUND`. That's the real payoff of knowing these functions: they compose. ```mermaid flowchart LR O["orders"] --> T["DATE_TRUNC
group by month"] T --> S["SUM
monthly revenue"] S --> L["LAG
previous month"] L --> N["NULLIF
avoid divide by zero"] N --> R["ROUND
percent change"] ``` * * * ## Which Function Do I Need? When you're stuck, start from the problem you're trying to solve: ```mermaid 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 | `COUNT()` | Counts rows or non-NULL values | | 2 | `SUM()` | Totals numeric values | | 3 | `AVG()` | Calculates the mean (ignores NULLs) | | 4 | `MIN()` / `MAX()` | Finds smallest / largest value | | 5 | `ROUND()` | Rounds to N decimal places | | 6 | `COALESCE()` | Returns first non-NULL value | | 7 | `NULLIF()` | Returns NULL when two values match | | 8 | `CAST()` | Converts data types | | 9 | `CONCAT()` | Joins strings | | 10 | `SUBSTRING()` | Extracts part of a string | | 11 | `TRIM()` / `UPPER()` / `LOWER()` | Cleans and normalizes text | | 12 | `REPLACE()` | Substitutes text | | 13 | `DATE_TRUNC()` / `EXTRACT()` | Buckets or splits dates | | 14 | `ROW_NUMBER()` | Numbers rows within groups | | 15 | `LAG()` / `LEAD()` | 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.**