Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A SQL function is a named operation you call inside a query expression. It does one of three jobs: it transforms a single value (a scalar function), it collapses a set of rows into one summary value (an aggregate function used with GROUP BY), or it calculates across related rows while leaving every row in the result (a window function, marked by OVER). Most of the mistakes readers make come from picking the wrong category for the job, or from assuming a function behaves the same way in every database.
What a SQL function is
A function is a named calculation that takes zero or more arguments and returns a result. Because it returns a result, it can sit wherever an expression is allowed: in the column list of a SELECT, inside a WHERE condition, in an ORDER BY, and so on. Microsoft’s SQL Server reference groups its built-in functions into conversion, date and time, JSON, logical, mathematical, metadata, security, string, and system families, and its overview is the place to start for that engine (Microsoft Learn, What Are the Microsoft SQL Database Functions?, SQL Server 17 view).
The useful distinction is not the family a function belongs to but how many rows it looks at. Three categories cover nearly everything you will write:
| Category | Input it works on | Output | Typical examples |
|---|---|---|---|
| Scalar | One row’s value or arguments | One value per row | UPPER, COALESCE, ROUND, date arithmetic helpers |
| Aggregate | A set of rows, usually split by GROUP BY | One value per group | COUNT, SUM, AVG, MIN, MAX |
| Window | A set of rows related to the current row, defined by OVER | One value per row, with all rows kept | ROW_NUMBER, running SUM … OVER, moving AVG |
The rest of this article uses one small table to show all three. Here it is:
#1 Best Overall
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT,
region TEXT,
amount NUMERIC
);
INSERT INTO orders VALUES
(1, 'Ada', 'East', 120),
(2, 'Ada', 'East', 80),
(3, 'Ben', 'West', 200),
(4, 'Ben', 'West', 50),
(5, 'Cy', 'East', 75);The CREATE and INSERT syntax above is generic. Adjust the column types for your engine.
Scalar functions: one input, one output
A scalar function returns a single value for each row it is evaluated on. It is the most common kind, and it is the one most likely to appear in a WHERE clause, a CASE expression, or a computed column.
Example: NULL-aware text
Suppose you want a display name that falls back through several columns. In SQLite, coalesce(X,Y,...) returns the first non-NULL argument, or NULL if every argument is NULL. SQLite’s concat(...) behaves differently: it ignores NULL arguments and returns an empty string when all of them are NULL (SQLite, Built-In Scalar SQL Functions). Those rules are documented for SQLite, and other engines may handle the same inputs differently.
SELECT COALESCE(nickname, first_name, 'Guest') AS display_name
FROM customers;
-- SQLite only: concat() skips NULL arguments
SELECT concat('Ada', NULL, 'Lovelace'); -- returns 'AdaLovelace'The concat result shows why NULL handling needs to be checked before you rely on a function. A missing middle name simply disappears instead of producing a NULL that would erase the whole string in a plain || concatenation.
Argument and return types matter
A function is not a black box. Microsoft’s string-function documentation notes that non-string arguments are implicitly converted to a text type, and that string results follow the collation rules of their inputs (Microsoft Learn, SQL Server 17 view). That means a numeric column passed to a string function may be converted without an error, and two text values can compare differently depending on collation. When results look wrong, check the types of the arguments first.
Aggregates and GROUP BY: many rows become one
An aggregate function evaluates a set of input values and returns one value. Paired with GROUP BY, it returns one row per group. Using the orders table above:
SELECT region,
SUM(amount) AS total_amount
FROM orders
GROUP BY region;| region | total_amount |
|---|---|
| East | 275 |
| West | 250 |
Five input rows became two output rows. Every column in the SELECT list that is not inside an aggregate must appear in GROUP BY, which is why the query above names region in both places.
Edge cases: empty sets and NULLs
MySQL’s reference for aggregate functions is a good example of behavior you should check. Its AVG() returns NULL when there are no matching rows, and also when the expression it averages is NULL (MySQL 26.7 Reference Manual, Aggregate Function Descriptions). A query that filters out every row therefore returns NULL, not zero. If your report needs zero, you must decide that explicitly with COALESCE.
Temporal values are a trap
MySQL also warns that SUM and AVG do not work directly with temporal values, because converting a date or time to a number loses content after the first nonnumeric character. The documented workaround is to convert to numeric units, aggregate, and convert back (MySQL 26.7 Reference Manual, Aggregate Function Descriptions). For example, to average durations, convert each interval to seconds first, then average the seconds and format the result as a duration.
Window functions: keep every row and add context
A window function calculates over a set of rows related to the current row, but it does not collapse them. SQLite identifies a window function by the presence of OVER: without it, the same name is an ordinary aggregate or scalar function. A windowed aggregate keeps the same number of output rows as the input, which is the key difference from GROUP BY (SQLite, Window Functions).
Two clauses inside OVER shape the calculation. PARTITION BY divides the rows into separate groups for the calculation, and an ORDER BY inside OVER sets the sequence the function sees. A frame specification can narrow the rows considered further.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →SELECT id,
region,
amount,
SUM(amount) OVER (PARTITION BY region ORDER BY id) AS running_total
FROM orders
ORDER BY id;| id | region | amount | running_total |
|---|---|---|---|
| 1 | East | 120 | 120 |
| 2 | East | 80 | 200 |
| 3 | West | 200 | 200 |
| 4 | West | 50 | 250 |
| 5 | East | 75 | 275 |
All five rows survive, and each carries its running total within its region. The final ORDER BY id controls display order only; the ordering inside OVER controls what the running sum accumulates. SQLite makes this difference explicit with row_number(), which is the simplest ranking example:
SELECT id,
region,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM orders;Here id 2 (amount 80) gets rank 2 in East and id 5 (amount 75) gets rank 3. Changing the order inside OVER changes the numbers, while the outer query can still list rows in any order.
Window functions have their own restrictions. SQLite states that window functions cannot use DISTINCT (SQLite, Window Functions). MySQL allows AVG as a window function when an OVER clause is supplied, but it cannot be combined with DISTINCT in that mode (MySQL 26.7 Reference Manual, Aggregate Function Descriptions).
Where a function can go in a SELECT
Expressions are not limited to the column list. MySQL’s function reference documents function and operator expressions in SELECT’s ORDER BY and HAVING clauses, and in WHERE clauses of SELECT, DELETE and UPDATE statements (MySQL 26.7 Reference Manual, Functions and Operators). PostgreSQL describes value expressions as usable in the target list of SELECT and in search conditions (PostgreSQL 18, Value Expressions).
Recommended Free Tools
Placement is where readers most often hit errors, because aggregate and window calls have extra rules. In PostgreSQL, the difference is clearest in the SELECT documentation: WHERE filters individual rows before grouping, while HAVING filters groups after GROUP BY has run (PostgreSQL 18, SELECT). The practical rules follow from that:
- Scalar functions can usually go in WHERE, HAVING, the select list and ORDER BY.
- Aggregates belong in the select list, HAVING or ORDER BY. In PostgreSQL, an aggregate call in WHERE is rejected with an error; move the condition to HAVING, or filter rows first in a subquery.
- Window calls are allowed in the select list and ORDER BY in PostgreSQL and SQLite. PostgreSQL does not allow them directly in WHERE, so filter on a window result by wrapping the query in a subquery or CTE.
-- Filter the rows first, then the groups
SELECT region,
SUM(amount) AS total
FROM orders
WHERE amount >= 75
GROUP BY region
HAVING SUM(amount) > 250;With the sample data, the WHERE clause removes id 4 (amount 50) before grouping, leaving East at 275 and West at 200. HAVING then keeps only East, the single group above 250.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why the same function behaves differently across databases
There is no single, universal implementation of “a SQL function.” PostgreSQL states that most functions and operators in its reference are not specified by the SQL standard, apart from trivial arithmetic and comparison operators and explicitly marked cases. Some extended functionality exists in other systems and may be compatible, but that is not a general portability promise (PostgreSQL 18, Functions and Operators). Before you reuse an example, confirm the name, argument order, and return type in your engine’s reference.
Version matters as well. SQLite’s concat_ws() was added in SQLite 3.50.0, released 2025-05-29 (SQLite, Built-In Scalar SQL Functions). A query that uses it fails on an older SQLite library, so if your application ships an older build, use concat() with explicit separators or check the version first.
A checklist before you copy a function into production
- Engine and version: the exact product and release the function is documented for.
- Name, argument count and order: confirm them in that engine’s reference, not in a similar product’s.
- Input and return types: note implicit conversions, precision, and collation for text.
- NULL and empty-set behavior: test with a NULL row and with a query that matches no rows.
- Date, time and zone behavior: especially for temporal arithmetic and aggregates over durations.
- Standard or vendor-specific: a name that looks familiar may behave differently from the standard definition.
- Scalar, aggregate or window, and where it can appear: check the clause placement rules before writing the query.
Further reading
For cross-database recipes, SQL Cookbook, 2nd Edition by Anthony Molinaro and Robert de Graaf is a practical SQL reference. O’Reilly lists the English edition as an intermediate-to-advanced, 567-page book published in November 2020, with examples for Oracle, DB2, SQL Server, MySQL and PostgreSQL, and recipes covering string handling and window functions (O’Reilly, SQL Cookbook, 2nd Edition). Its preface opens with the line “SQL is the lingua franca of the data professional” (O’Reilly, SQL Cookbook, 2nd Edition preface).
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
