SQL Functions — A Friendly Guide With Real Examples

When I was learning SQL, "functions" felt like a big scary word borrowed from programming. Turns out, they're just small helpers that do one job: take some data in, give you a changed version back out. Once that clicked, SQL got a lot less intimidating.

This article walks through the SQL functions you'll actually use, with real examples — an online store, a user table, a sales report — and notes on when each one is the right tool.

What Is a SQL Function?

Think of a function as a little machine. You feed it something (a column, a number, a piece of text), and it hands back a result. You don't need to know how it works inside, you just need to know what it takes in and what it gives back.

SELECT UPPER(name) FROM customers;

Here, UPPER is the machine. Feed it a name, it hands back the name in capital letters. That's the whole idea behind every function in this article.

SQL functions generally fall into a few groups:

• Aggregate functions — take many rows and turn them into one answer (like a total or an average).
• String functions — work on text.
• Date/time functions — work on dates and times.
• Numeric functions — do math.
• Window functions — a bit more advanced, but incredibly useful once you get them.

Let's go through each with examples.

1. Aggregate Functions

They look at a group of rows and give you back a single number.

COUNT() — how many?

SELECT COUNT(*) FROM orders;

Used any time you're asking "how many of these are there?" — how many orders came in today, how many users signed up this month, how many products are out of stock.

SUM() — add it all up

SELECT SUM(order_total) FROM orders WHERE order_date >= '2026-09-01';

Used when calculating totals example; total revenue this month, total items sold, total hours logged.

AVG() — the average

SELECT AVG(order_total) FROM orders;

Used anytime "on average" comes up in a question. It could be average order size, average time to respond to a support ticket or average rating on a product.

MIN() and MAX() — the smallest and biggest

SELECT MIN(order_total) AS cheapest, MAX(order_total) AS priciest
FROM orders;

Used when finding extremes like the cheapest order ever placed, the newest signup, the oldest unpaid invoice.

Grouping it all together with GROUP BY

Aggregate functions get really useful once you pair them with GROUP BY. Say you want total sales per month, not just one giant total:

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(order_total) AS total_sales,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

Used with basically any report. "Sales by month," "orders by customer," "signups by country" all of these are a GROUP BY with one or more aggregate functions attached.

2.String Functions

Text is messy. People type names in weird cases, leave extra spaces, or you need to combine two columns into one readable label. String functions clean this up.

CONCAT() — joining text together

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

It is used when building a display name, combining an address from separate columns, making a readable label out of pieces of data.

UPPER() and LOWER() — changing case

SELECT email FROM customers WHERE LOWER(email) = '[e-mail]';

Used when comparing text without worrying about capital letters. This is a common one, someone might type [e-mail] at signup, and you still want to match it against [e-mail] later.

TRIM() — removing extra spaces

SELECT TRIM(coupon_code) FROM orders;

Used when cleaning up data that came from a form or an import file, where people (or systems) often leave a stray space at the start or end.

LENGTH() — how long is this text?

SELECT name FROM products WHERE LENGTH(name) > 50;

Used when catching data problems, like a product name that's way too long to display properly, or a password that's too short.

SUBSTRING() — grabbing part of a text value

SELECT SUBSTRING(phone_number, 1, 3) AS area_code
FROM customers;

We can use it when pulling out a piece of a larger value, like an area code from a phone number, or the first few letters of a product code.

REPLACE() — swapping text

SELECT REPLACE(address, 'St.', 'Street') FROM customers;

Can be used when fixing inconsistent data, like standardizing abbreviations before generating a report.

3.Date and Time Functions

Dates come up constantly: "when did this happen," "how long ago was that," "what month was this in." These functions make dates usable.

NOW() / CURRENT_DATE — right now

SELECT * FROM subscriptions WHERE renewal_date < CURRENT_DATE;

anything that depends on "today" — overdue payments, expired trials, upcoming renewals.

DATE_TRUNC() — rounding a date down to a unit

SELECT DATE_TRUNC('month', signup_date) AS signup_month, COUNT(*)
FROM users
GROUP BY 1;

We can use it when grouping data by day, week, or month. This is one of the most useful functions for building reports — it turns a messy timestamp into a clean bucket you can group by.

DATEDIFF() / date subtraction — how much time passed

SELECT
  name,
  CURRENT_DATE - signup_date AS days_since_signup
FROM users;

(Note: the exact syntax for this varies a bit by database — Postgres lets you subtract dates directly like above, while MySQL and SQL Server use DATEDIFF().)

Used to calculating someone's account age, how many days a task has been open, or how long it's been since a customer's last order.

EXTRACT() — pulling out one piece of a date

SELECT EXTRACT(DOW FROM order_date) AS day_of_week, COUNT(*)
FROM orders
GROUP BY 1;

Used when finding patterns, like "do we get more orders on weekends?" DOW here means day of week.

4. Numeric Functions — Basic Math

These are the functions you reach for when you need to do a bit of arithmetic inside your query, instead of after the fact.

ROUND() — rounding numbers

SELECT ROUND(AVG(order_total), 2) AS avg_order
FROM orders;

Used when cleaning up decimals for a report. Nobody wants to see 47.836218 when 47.84 is what actually matters.

ABS() — absolute value

SELECT ABS(balance) AS balance_owed FROM accounts WHERE balance < 0;

Used when working with numbers that can be negative, like a balance or a difference, when you just care about the size of the number, not the sign.

5. Window Functions

Window functions look a bit different, they solve problems that are painful otherwise unlike aggregate functions, they don't collapse rows into one — they add a calculated value next to each row.

ROW_NUMBER() — numbering rows

SELECT
  name,
  order_total,
  ROW_NUMBER() OVER (ORDER BY order_total DESC) AS rank
FROM orders;

Used when ranking things, like "who are our top 10 spenders" or "what's the 3rd most recent order for each customer."

RANK() and DENSE_RANK() — ranking with ties

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM quiz_results;

example — "top order per customer"

Say you want each customer's biggest order, but you still want to see their name and every order next to it:

SELECT *
FROM (
  SELECT
    customer_id,
    order_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

Used anytime you want "the top result per group", the newest order per customer, the highest score per student, the latest login per user. This is genuinely hard to do without a window function, and once you learn this pattern, you'll reuse it constantly.

Final thoughts

SQL functions aren't complicated once you see them as small, single-purpose helpers. Aggregate functions turn many rows into one number. String functions clean up and shape text. Date functions make "when" usable. Window functions let you rank and compare rows without losing the details.

You don't need to memorize all of them right away. Start with COUNT, SUM, AVG, and DATE_TRUNC, you'll use those constantly. The rest will stick as you run into the problems they solve