What aggregate functions does SQL provide, and what are the subtle behaviours of MIN/MAX on non-numeric types?

How to think about it

The interviewer is probing the edges, not the definitions — specifically how NULLs flow through aggregates and what MIN/MAX really do on dates and strings. Anyone can recite the five functions; the ones who stand out reason through a tricky NULL case on the spot.

Function	Input	NULLs	Notes
COUNT(*)	any	counted	counts rows
COUNT(col)	any	skipped	counts non-NULL values
SUM(col)	numeric	skipped	NULL if every input is NULL
AVG(col)	numeric	skipped	denominator = non-NULL count
MIN(col)	any orderable	skipped	works on strings, dates
MAX(col)	any orderable	skipped	works on strings, dates

A worked example

One query exercises every edge at once — note that Eggplant has a NULL price:

-- products(name, category, price, launched_on)
-- 'Eggplant' has a NULL price; names mix upper- and lower-case

SELECT COUNT(*)                AS total_rows,       -- all 5 rows
       COUNT(price)            AS rows_with_price,  -- skips the NULL
       ROUND(AVG(price), 2)    AS avg_price,        -- divides by 4, not 5
       MIN(name)               AS first_alpha,
       MAX(name)               AS last_alpha,
       MIN(launched_on)        AS earliest_launch,  -- string dates sort correctly
       COALESCE(SUM(price), 0) AS total_price_safe
FROM products;

total_rows	rows_with_price	avg_price	first_alpha	last_alpha	earliest_launch	total_price_safe
5	4	1.63	Apple	cherry	2022-11-10	6.5

Three things to read off it. COUNT(*) is 5 but COUNT(price) is 4 — the NULL price is skipped, and AVG divides by that same 4, not by 5 (a classic source of “why is my average too high?”). MIN(name) is Apple and MAX(name) is cherry — a string comparison, and because uppercase sorts before lowercase in this collation, lowercase cherry lands last. And MIN(launched_on) works straight off the ISO date strings, since '2022-...' sorts before '2023-...' lexicographically.

MIN/MAX on non-numerics

That last point is the one that surprises people: MIN/MAX work on any type with a defined sort order — dates, timestamps, strings. For strings the comparison is lexicographic, following the column’s collation, so whether 'Apple' < 'banana' holds depends on whether that collation is case-sensitive.

The integer-division gotcha

On some engines (older PostgreSQL with an integer column), AVG over integers does integer division and truncates the decimal. Cast to be safe:

SELECT AVG(CAST(price AS NUMERIC)) FROM products;   -- safe across engines