{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"15-sql-functions-you-should-know-x9sa9","url":"https://zyvop.com/15-sql-functions-you-should-know-x9sa9","title":"15 SQL Functions You Should Know","subtitle":"Master the aggregate, string, date, and window functions behind most real-world queries, with simple diagrams that show what each one does.","tldr":"A small set of SQL functions does most of the heavy lifting in everyday queries. This guide covers 15 essentials, from COUNT, COALESCE, and CAST to DATE_TRUNC, ROW_NUMBER, and LAG, with practical examples and Mermaid diagrams that make each one easy to understand.","keywords":["Window Functions","SQL Functions","SQL","data-analysis","databases","Databases"],"entities":["Sanju Singh","Window Functions","SQL Functions","SQL","data-analysis","databases","Databases","ZyVOP"],"keyTakeaways":["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."],"headings":["The Sample Data","Aggregate Functions","1. COUNT()","2. SUM()","3. AVG()","4. MIN() and MAX()","Numeric and NULL-Handling Functions","5. ROUND()","6. COALESCE()","7. NULLIF()","8. CAST()","String Functions","9. CONCAT()","10. SUBSTRING()","11. TRIM(), UPPER(), and LOWER()","12. REPLACE()","Date Functions","13. DATE_TRUNC() and EXTRACT()","Window Functions","14. ROW_NUMBER()","15. LAG() and LEAD()","Which Function Do I Need?","Quick Reference","Final Thoughts"],"outboundLinks":[],"contentText":"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\"] --&gt; G[\"GROUP BY customer_id\"] G --&gt; S[\"SUM: lifetime value\"] G --&gt; AV[\"AVG: average order size\"] G --&gt; M[\"MIN / MAX: first and latest order\"] G --&gt; 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:&lt;br/&gt;London, London, NULL, Paris\"] --&gt; A[\"COUNT(*) = 4&lt;br/&gt;every row\"] D --&gt; B[\"COUNT(city) = 3&lt;br/&gt;skips the NULL\"] D --&gt; C[\"COUNT(DISTINCT city) = 2&lt;br/&gt;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 --&gt; P[\"Return phone\"] A -- Yes --&gt; B{\"email is NULL?\"} B -- No --&gt; E[\"Return email\"] B -- Yes --&gt; 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)\"] --&gt; B{\"order_count = 0?\"} B -- Yes --&gt; C[\"Returns NULL\"] B -- No --&gt; D[\"Returns order_count\"] C --&gt; E[\"SUM / NULL = NULL&lt;br/&gt;no error\"] D --&gt; F[\"SUM / order_count&lt;br/&gt;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\"] --&gt; B[\"Integer division&lt;br/&gt;result: 1\"] C[\"CAST(3 AS DECIMAL) / 2\"] --&gt; D[\"Decimal division&lt;br/&gt;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[\"' Anna@Example.com '\"] --&gt;|TRIM| B[\"'Anna@Example.com'\"] B --&gt;|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. 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'\"] --&gt;|\"SUBSTRING(phone, 1, 3)\"| A[\"'555'\"] P --&gt;|\"REPLACE(phone, '-', '')\"| B[\"'5551234567'\"] F[\"'Anna' and 'Lee'\"] --&gt;|\"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 --&gt; M1 B --&gt; M1 C --&gt; M2 M1 --&gt; R1 M2 --&gt; 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 --&gt; R[\"WHERE rn = 1&lt;br/&gt;latest order per customer\"] B1 --&gt; 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&lt;br/&gt;revenue: 1000&lt;br/&gt;prev: NULL\"] F[\"Feb&lt;br/&gt;revenue: 1200&lt;br/&gt;prev: 1000&lt;br/&gt;change: +20%\"] M[\"Mar&lt;br/&gt;revenue: 1080&lt;br/&gt;prev: 1200&lt;br/&gt;change: -10%\"] J --&gt; F --&gt; M F -.-&gt;|LAG looks back| J M -.-&gt;|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\"] --&gt; T[\"DATE_TRUNC&lt;br/&gt;group by month\"] T --&gt; S[\"SUM&lt;br/&gt;monthly revenue\"] S --&gt; L[\"LAG&lt;br/&gt;previous month\"] L --&gt; N[\"NULLIF&lt;br/&gt;avoid divide by zero\"] N --&gt; R[\"ROUND&lt;br/&gt;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 --&gt; A[\"Summarize many rows\"] Q --&gt; B[\"Handle NULLs or data types\"] Q --&gt; C[\"Clean or build text\"] Q --&gt; D[\"Work with dates\"] Q --&gt; E[\"Compare rows to each other\"] A --&gt; A1[\"COUNT, SUM, AVG, MIN, MAX\"] B --&gt; B1[\"COALESCE, NULLIF, CAST, ROUND\"] C --&gt; C1[\"CONCAT, SUBSTRING, TRIM, UPPER, LOWER, REPLACE\"] D --&gt; D1[\"DATE_TRUNC, EXTRACT\"] E --&gt; 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.","contentHash":"sha256:8be1151accced58e9bfc71b3e37826a5847bbca7283c37a3f723d989e171013b","authorName":"Sanju Singh","authorUrl":"https://zyvop.com/author/sanjay687","authorSameAs":[],"category":"Databases","tags":["Window Functions","SQL Functions","SQL","data-analysis","databases"],"audience":"Software engineers and developers building applications with Databases","tone":"Instructional, practical, code-first","readingTimeMinutes":7,"wordCount":1819,"faqs":null,"primaryTopic":"Databases","publishedAt":"2026-11-18T04:25:58.787Z","updatedAt":"2026-11-18T04:25:58.787Z","canonicalUrl":"https://zyvop.com/15-sql-functions-you-should-know-x9sa9"}