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.

·
7 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

15 SQL Functions You Should Know

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 / LEAD

The 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.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.

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 --> R2

EXTRACT 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 --> 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.

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| 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.

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

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.

Comments(0)

Join the discussion by logging into your account.

No comments yet. Be the first to comment!

Sanju Singh

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Sanju Singh's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.