What aggregate functions does SQL provide, and what are the subtle behaviours of MIN/MAX on non-numeric types?
SQL's standard aggregate functions are COUNT, SUM, AVG, MIN, and MAX. MIN and MAX work on any orderable type including strings and dates, using the type's collation or sort order, which surprises analysts who expect numeric-only semantics.
How to think about it
The interviewer is probing whether you know the subtle edges — specifically NULL handling and what MIN/MAX actually do on dates and strings. Most candidates know the definitions; the ones who stand out can reason through a tricky NULL scenario on the spot.
The five standard aggregates
| Function | Input type | NULL handling | Notes |
|---|---|---|---|
| COUNT(*) | any | includes NULLs | counts rows |
| COUNT(col) | any | skips NULLs | counts non-NULL values |
| SUM(col) | numeric | skips NULLs | returns NULL if all inputs NULL |
| AVG(col) | numeric | skips NULLs | denominator = non-NULL count only |
| MIN(col) | any orderable | skips NULLs | works on strings, dates |
| MAX(col) | any orderable | skips NULLs | works on strings, dates |
MIN/MAX on non-numerics
This is the part that surprises people. MIN and MAX work on any type that has a defined sort order — dates, timestamps, and strings all qualify.
For strings, the comparison is lexicographic (alphabetical), following the column’s collation. That means 'banana' is greater than 'apple' but whether 'Apple' is less than 'banana' depends on whether the collation is case-sensitive.
SUM/AVG return NULL on an empty group
This trips people up constantly. When no rows match, SUM and AVG return NULL, not zero. Wrap them with COALESCE if downstream code expects a number.
The AVG integer-division gotcha
On some databases (older PostgreSQL with integer columns), AVG on an integer column uses integer division and truncates the decimal. Cast explicitly to be safe:
-- Safe across all engines
SELECT AVG(CAST(price AS NUMERIC)) FROM products;