SQL Functions: The Toolbox Hiding Inside Every SELECT

Most of what a database does to your data happens between SELECT and FROM: turning a name into an email address, a date into a month, a pile of raw numbers into an average worth reporting. That work is done by functions, and most SQL beginners use a handful of them for months, COUNT, SUM, maybe UPPER, without ever noticing that "function" is a real, well-defined idea with its own categories and rules.

We'll stay with the beekeeping co-op from the earlier articles I wrote here and here. The notes column below is new; everything else should look familiar. Here's the script that added it:

ALTER TABLE harvests ADD COLUMN notes VARCHAR(200);

UPDATE harvests SET notes = '  Extracted in the afternoon, slight rain  ' WHERE harvest_id = 'H1';
UPDATE harvests SET notes = 'Morning harvest, strong yield'                WHERE harvest_id = 'H2';
UPDATE harvests SET notes = '  BUMPER CROP after spring bloom  '           WHERE harvest_id = 'H3';
UPDATE harvests SET notes = 'Logged by hand, hive number unclear'         WHERE harvest_id = 'H4';

The Data, With One Addition

beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL
5	Grace Achieng	2

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg	notes
H1	101	2024-05-01	12.5	" Extracted in the afternoon, slight rain "
H2	101	2024-09-01	9.0	"Morning harvest, strong yield"
H3	102	2024-06-15	15.0	" BUMPER CROP after spring bloom "
H4	999	2024-07-01	5.0	"Logged by hand, hive number unclear"

Whoever logs harvests by hand isn't consistent about spacing or capitalization. That's not an accident on my part, real free-text fields look exactly like this, and it gives the string functions below something honest to clean up.

What a Function Actually Is

A SQL function takes input, does something deterministic to it, and returns a value. That's the whole definition, but it hides an important split. Functions fall into three behaviors, and confusing them is where most beginner mistakes come from.

Scalar functions run once per row and return one value per row. UPPER('bee') doesn't care what else is in the table; feed it a string, it hands back a string, row by row.

Aggregate functions collapse many rows into one. SUM(honey_kg) doesn't return a value for each row, it consumes the whole group and returns a single number for it. This is why you can't mix a raw column with an aggregate function in the same SELECT without a GROUP BY, the database can't return one row of totals sitting next to twenty rows of detail at the same time.

Window functions are the middle ground: they look across a group of rows, like an aggregate does, but they return a value for every row instead of collapsing them. You'll see the difference directly in the examples below.

Scalar Functions: Cleaning and Shaping Text

The co-op needs a [e-mail] email address for everyone, generated from their name rather than typed in by hand.

SELECT name,
    LOWER(REPLACE(name, ' ', '.')) || '@murchisonapiary.coop' AS coop_email
FROM beekeepers;

Result:

name	coop_email
Amara Wanjiru	[e-mail]
Brian Otieno	[e-mail]
Chiara Mwangi	[e-mail]
David Kimani	[e-mail]
Grace Achieng	[e-mail]

Three scalar functions, nested inside each other, run on every row: REPLACE swaps the space for a period, LOWER normalizes the case, and || glues the domain on the end. (MySQL doesn't support || for concatenation by default; use CONCAT() there instead.)

Now clean up the messy notes field:

SELECT harvest_id, TRIM(notes) AS clean_note, LENGTH(TRIM(notes)) AS note_length
FROM harvests;

Result:

harvest_id	clean_note	note_length
H1	Extracted in the afternoon, slight rain	39
H2	Morning harvest, strong yield	29
H3	BUMPER CROP after spring bloom	30
H4	Logged by hand, hive number unclear	35

TRIM removes the leading and trailing spaces someone left when they typed H1 and H3 by hand; LENGTH then measures the cleaned-up result. Run LENGTH before TRIM and H1 would report 43, the padding spaces counted as real characters. The order you nest functions in changes the answer, not just the syntax.

Aggregate Functions: Turning Rows Into a Summary

SELECT
    COUNT(*) AS harvest_count,
    SUM(honey_kg) AS total_kg,
    ROUND(AVG(honey_kg), 2) AS avg_kg,
    MIN(honey_kg) AS smallest_kg,
    MAX(honey_kg) AS largest_kg
FROM harvests;

Result:

harvest_count	total_kg	avg_kg	smallest_kg	largest_kg
4	41.5	10.38	5.0	15.0

Four rows go in, one row comes out. That's the aggregate signature: COUNT, SUM, AVG, MIN, and MAX all take a column and reduce it, whatever number of rows they're given, to a single number. Notice ROUND wrapping AVG here too, a scalar function applied to the result of an aggregate one. Functions compose like this constantly; there's no rule against feeding one function's output into another.

Aggregates get more useful paired with GROUP BY, which runs the same collapse once per group instead of once for the whole table:

SELECT hive_id,
    SUM(honey_kg) AS total_kg,
    FLOOR(SUM(honey_kg) * 1000 / 350) AS jars_of_350g
FROM harvests
GROUP BY hive_id
ORDER BY hive_id;

Result:

hive_id	total_kg	jars_of_350g
101	21.5	61
102	15.0	42
999	5.0	14

SUM collapses each hive's harvests into a total, and FLOOR figures out how many complete 350-gram jars that total fills, rounding down because a co-op can't sell three-quarters of a jar. Hive 999 shows up here because this query only looks at harvests, a reminder from the joins article that this hive_id has no matching row in hives at all.

Date Functions: Reading Time Out of a Timestamp

SELECT harvest_id, harvest_date, EXTRACT(MONTH FROM harvest_date) AS harvest_month
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	harvest_month
H1	2024-05-01	5
H3	2024-06-15	6
H4	2024-07-01	7
H2	2024-09-01	9

EXTRACT pulls a single component out of a date, useful the moment you want to group harvests by month rather than by exact day, spotting a June bloom pattern buried inside four rows is impossible by eye, but trivial once EXTRACT(MONTH FROM ...) feeds into a GROUP BY.

Dates also support direct arithmetic. Assume today is 2024-10-01, and the co-op wants to know how stale each harvest log is:

SELECT harvest_id, harvest_date, ('2024-10-01' - harvest_date) AS days_since_harvest
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	days_since_harvest
H1	2024-05-01	153
H3	2024-06-15	108
H4	2024-07-01	92
H2	2024-09-01	30

Subtracting two dates in PostgreSQL returns a plain integer number of days. MySQL wants DATEDIFF('2024-10-01', harvest_date), and SQL Server wants DATEDIFF(day, harvest_date, '2024-10-01'), same idea, different name and argument order per engine.

Conditional Functions: Branching Without Leaving SQL

SELECT harvest_id, honey_kg,
    CASE
        WHEN honey_kg < 8  THEN 'Small'
        WHEN honey_kg < 13 THEN 'Medium'
        ELSE 'Large'
    END AS harvest_size
FROM harvests
ORDER BY honey_kg;

Result:

harvest_id	honey_kg	harvest_size
H4	5.0	Small
H2	9.0	Medium
H1	12.5	Medium
H3	15.0	Large

CASE checks its conditions top to bottom and stops at the first match, which is why the boundaries (< 8, < 13) don't need to repeat the lower bound of each bucket.

COALESCE solves a narrower, more common problem: what to display in place of a NULL.

SELECT b.name, COALESCE(m.name, 'Founding member') AS mentor_name
FROM beekeepers b
LEFT JOIN beekeepers m ON b.mentor_id = m.keeper_id;

Result:

name	mentor_name
Amara Wanjiru	Founding member
Brian Otieno	Amara Wanjiru
Chiara Mwangi	Amara Wanjiru
David Kimani	Founding member
Grace Achieng	Brian Otieno

The self join finds each keeper's mentor by name, and returns NULL for the two keepers who don't have one. COALESCE takes that NULL and replaces it with the first non-null value in its argument list, here just one fallback string, though COALESCE accepts as many candidates as you give it and returns the first one that isn't NULL.

Window Functions: Per-Row Answers That Still See the Group

SELECT hive_id,
    SUM(honey_kg) AS total_kg,
    RANK() OVER (ORDER BY SUM(honey_kg) DESC) AS honey_rank
FROM harvests
GROUP BY hive_id;

Result:

hive_id	total_kg	honey_rank
101	21.5	1
102	15.0	2
999	5.0	3

This looks like the GROUP BY query from earlier, and it starts the same way, but RANK() OVER (...) doesn't collapse anything further. It ranks each already-grouped row against the others and keeps every row visible. Try to get a rank number next to a raw total using only GROUP BY and an aggregate, and you can't; the aggregate would need to already know every group's total before ranking any of them, which is exactly the problem window functions exist to solve.

The same idea works without collapsing rows at all, tracking a running total across a hive's season:

SELECT harvest_id, harvest_date, honey_kg,
    SUM(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date) AS running_total_kg
FROM harvests
WHERE hive_id = 101
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	running_total_kg
H1	2024-05-01	12.5	12.5
H2	2024-09-01	9.0	21.5

Both rows from hive 101 survive, unlike a plain SUM(honey_kg) GROUP BY hive_id, which would flatten them into a single 21.5 and lose the harvest-by-harvest detail. PARTITION BY restarts the running total for each hive, and ORDER BY inside the OVER() clause decides the order the total accumulates in.

When to Reach for Each Kind

You need to...	Use
Clean, reformat, or combine text on every row	Scalar string functions
Do arithmetic or rounding on every row	Scalar numeric functions
Collapse many rows into one summary number	Aggregate functions
Pull a component out of a date, or measure a time gap	Date functions
Branch on a condition, or fill in a value for NULL	CASE / COALESCE
Rank, running-total, or compare rows without losing any rows	Window functions

Try It Yourself

Take the notes column and write a query that extracts just the first word of each note, using whatever substring function your database offers, SPLIT_PART, SUBSTRING_INDEX, or LEFT combined with a search for the first space. It's a small problem, but it forces you to nest two or three scalar functions correctly, which is the skill this whole article has really been about.