What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A SQL function is a named operation you call inside a query expression. It can change a single value (trim a string, replace a NULL), summarize a set of rows (total sales per region), or calculate across neighboring rows while keeping every row in the output. Choosing the right one comes down to two questions: does the function see one value or many rows, and does your database engine accept it in the clause where you want to use it? Syntax and behavior differ between engines, so the examples below name the engine and, where it matters, the version.
Three kinds of functions, one question: how many rows does it see?
Most SQL functions fall into three groups. The difference between them is what the function receives and how many rows come back.
As an Amazon Associate I earn from qualifying purchases.
| Kind | Input it works on | Output rows | Typical location | Example |
|---|---|---|---|---|
| Scalar | One row’s value or arguments | One value per input row | Select list, WHERE, ORDER BY, and most other expression positions | trim(rep) |
| Aggregate | A set of rows, usually split by GROUP BY | One value per group | Select list, HAVING, ORDER BY (not WHERE) | SUM(amount) with GROUP BY region |
| Window | A window of related rows, defined by OVER | One value per input row, with all rows kept | Select list and ORDER BY in the engines documented here | SUM(amount) OVER (PARTITION BY region ORDER BY sold_on) |
Microsoft describes scalar functions as usable wherever an expression is valid, and aggregates as calculating over a set of rows to return one value. Window functions are the case most readers have not met yet, so they get their own section below.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Scalar functions: one value in, one value out
A scalar function takes one or more input arguments and returns a single value. Because it runs once per row, it can sit in the select list, in a WHERE condition, or in an ORDER BY expression. Common families include string, mathematical, conversion, date and time, conditional, and JSON functions. SQL Server’s reference also lists logical, metadata, security, and system functions.
#1 Best Overall
NULL-aware text in SQLite
NULL handling is where scalar functions most often surprise people. SQLite documents these specific behaviors, which should not be assumed for other engines:
coalesce(X,Y,...)returns the first non-NULL argument, or NULL if every argument is NULL.concat(...)ignores NULL arguments, soconcat('a', NULL, 'b')returnsab. If every argument is NULL, it returns an empty string.- The
||operator returns NULL when either operand is NULL. The same input therefore produces a different result depending on whether you useconcator||.
-- SQLite
SELECT concat(rep, ' / ', region) AS label_concat,
rep || ' / ' || region AS label_operator,
coalesce(amount, 0.0) AS amount_or_zero
FROM sales;If a region is NULL, the first column still shows the rep’s name, while the second column becomes NULL. Decide which behavior you want before you build a report on either one.
Argument types and implicit conversion
Function arguments and return types matter as much as the name. Microsoft’s SQL Server reference states that string functions implicitly convert non-string arguments to a text type, and that string results follow the collation rules of their inputs. A number that you assume is text may be converted in a way you did not intend, so check the types of columns passed into a function, especially when mixing numbers and strings.
Free tools Windows power users keep installed
One-click scans. No signup required.
Aggregates: collapsing rows with GROUP BY
An aggregate function reads many input rows and returns one value. Without GROUP BY, the whole result set becomes one summary row. With GROUP BY, each distinct group gets its own summary row. The usual examples are COUNT, SUM, AVG, MIN, and MAX. Their exact edge-case behavior is vendor-specific, so confirm it in your engine’s reference.
The examples in this article use one small table:
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
region TEXT,
rep TEXT,
amount REAL,
sold_on TEXT -- ISO date stored as text in this example
);
INSERT INTO sales VALUES
(1, 'East', 'Ana', 120.0, '2026-01-05'),
(2, 'East', 'Ben', 80.0, '2026-01-09'),
(3, 'West', 'Cruz', 200.0, '2026-01-11'),
(4, 'West', 'Dee', NULL, '2026-01-15'),
(5, 'West', 'Eli', 150.0, '2026-02-02');-- SQLite
SELECT region,
COUNT(*) AS deals,
COUNT(amount) AS priced,
SUM(amount) AS total,
AVG(amount) AS avg_amount
FROM sales
GROUP BY region;In SQLite, this returns two rows: East with 2 deals, 2 priced, a total of 200.0, and an average of 100.0; and West with 3 deals, 2 priced, a total of 350.0, and an average of 175.0.
The difference between COUNT(*) and COUNT(amount) is the main thing to take from this query. COUNT(*) counts rows. COUNT(amount) counts only rows where amount is not NULL. Dee’s row counts as a deal but not as a priced deal, and SUM and AVG both skip her NULL amount.
Filtering groups with HAVING
PostgreSQL’s SELECT reference draws the line clearly: WHERE filters individual rows before grouping, and HAVING filters groups after grouping. Conditions on plain columns go in WHERE. Conditions on aggregate results go in HAVING. Aggregate calls are not allowed in WHERE.
-- SQLite
SELECT region, SUM(amount) AS total
FROM sales
WHERE sold_on >= '2026-01-10'
GROUP BY region
HAVING SUM(amount) > 100;This returns one row: West, 350.0. The date filter removes Ana and Ben’s rows before grouping, and the HAVING clause keeps only groups whose total exceeds 100.
NULL and empty-set behavior in MySQL
MySQL’s aggregate reference shows why edge cases need checking. AVG() returns NULL when there are no matching rows, and also when its expression is NULL. An aggregate over an empty set is therefore not the same thing as zero.
MySQL also warns that SUM() and AVG() do not work directly on temporal values, because conversion to a number loses content after the first nonnumeric character. The documented workaround is to convert to numeric units, aggregate, and convert the result back. For a column of durations in MySQL, that looks like this:
-- MySQL
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS total_duration
FROM shifts;Window functions: keeping every row
A window function calculates over a set of rows related to the current row, but it does not collapse them. Every input row still appears in the output. SQLite identifies window functions by the presence of OVER. Without it, the same function name is an ordinary aggregate or scalar function.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsOVER, PARTITION BY, and frames
The OVER clause defines the window. PARTITION BY splits the rows into groups, and each group is calculated separately. The frame specification defines which rows inside the partition take part in each calculation. Without an explicit frame, SQLite’s default frame runs from the start of the partition to the current row, which is why a running total appears when you add ORDER BY. The SQLite window functions page describes these rules in full.
Ranking and running totals with the same data
-- SQLite
SELECT id,
region,
rep,
amount,
row_number() OVER (PARTITION BY region ORDER BY sold_on) AS rn,
SUM(amount) OVER (PARTITION BY region ORDER BY sold_on) AS running_total
FROM sales
ORDER BY region, sold_on;| id | region | rep | amount | rn | running_total |
|---|---|---|---|---|---|
| 1 | East | Ana | 120.0 | 1 | 120.0 |
| 2 | East | Ben | 80.0 | 2 | 200.0 |
| 3 | West | Cruz | 200.0 | 1 | 200.0 |
| 4 | West | Dee | NULL | 2 | 200.0 |
| 5 | West | Eli | 150.0 | 3 | 350.0 |
All five input rows survive. The output has the same row count as the input, unlike the GROUP BY query, which returned two rows. Dee’s NULL amount does not reduce the running total, because SUM ignores NULLs, so her running total stays at 200.0 until Eli’s row adds 150.
ORDER BY inside OVER and ORDER BY at the end
These two ORDER BY clauses do different jobs. The ORDER BY inside OVER sets the sequence used by the calculation. In the query above, it determines which row counts as first in each region. The ORDER BY at the end of the statement controls only how the final result is displayed. Remove the final ORDER BY and you may get the same numbers in a different row order, but the window sequence is unchanged.
Restrictions to check
- SQLite says window functions cannot use DISTINCT.
- In MySQL,
AVG()can be used as a window function with OVER, but the reference says it cannot be combined with DISTINCT in that mode. - PostgreSQL allows window calls in the SELECT list and in ORDER BY. Confirm the same placement rules in your engine before copying a query.
Where a function can appear in a query
MySQL’s reference documents function and operator expressions in several places, including SELECT’s ORDER BY and HAVING clauses and in WHERE clauses of SELECT, DELETE, and UPDATE statements. PostgreSQL describes value expressions as usable in contexts that include the SELECT target list and search conditions. In practice, a scalar function is the most flexible, because it can appear almost anywhere an expression can. Aggregates belong in the select list, HAVING, or ORDER BY, and window calls belong in the select list or ORDER BY, depending on the engine.
Function families at a glance
| Family | What it does | Named examples in the official references | Notes to check |
|---|---|---|---|
| String | Trims, searches, joins, and formats text | SQLite: instr, trim, concat, concat_ws, format; SQL Server: string category |
SQLite added concat_ws() in version 3.50.0, released 2025-05-29, so older SQLite builds will not run it. SQL Server converts non-string arguments implicitly. |
| Numeric | Absolute values, rounding, and other math | SQLite: abs; SQL Server: mathematical category |
Rounding and precision rules are engine-specific. Check your engine’s reference. |
| Date and time | Extracts parts, computes intervals, and formats dates | SQLite: documented in its separate date and time section; SQL Server: date and time category | Calendar, time zone, and interval behavior differ by engine. MySQL’s SUM and AVG do not work directly on temporal values. |
| Conversion | Changes a value’s data type | SQL Server: conversion category | Implicit and explicit conversions can produce different results. Check your engine’s conversion rules. |
| Conditional and NULL handling | Chooses a value based on a condition or NULLs | SQLite: coalesce; SQL Server: logical category |
SQLite’s coalesce behavior is described above. CASE expressions are part of standard SQL. |
| JSON | Reads or builds JSON data | SQL Server: JSON category; SQLite: JSON functions documented separately | Function sets and version support differ across engines. |
Why a function works in one database but not another
Most SQL function behavior is not uniformly portable. PostgreSQL’s reference states that most of its functions and operators, apart from trivial arithmetic and comparison and explicitly marked cases, are not specified by the SQL standard. Some functions exist in other systems in compatible form, but that is not a general portability promise. Before reusing a query, check the following:
- Engine and version. A function may be missing or may behave differently in an older release.
- Name, argument count, and argument order. Two functions with similar names can take their arguments in different orders.
- Input and return types. Watch for implicit conversion, precision, and collation.
- NULL and empty-set behavior.
COUNT(*),COUNT(column), andAVG()over no rows differ in ways that change results. - Date, time, and calendar behavior. Time zone and interval handling are engine-specific.
- Standard or vendor-specific. A function with a familiar name may be a vendor extension.
- Scalar, aggregate, or window, and where it may appear. A function valid in the select list may be rejected in WHERE.
The concat_ws example shows the version point. A query that uses concat_ws() needs SQLite 3.50.0 or later. When you need broader compatibility, use concat with an explicit separator or write the join with ||, keeping in mind the NULL behavior described earlier.
Further reading
For cross-database recipes, SQL Cookbook, 2nd Edition by Anthony Molinaro and Robert de Graaf is a practical 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. Its topics include string handling and expanded window-function recipes. The publisher’s preface opens with the line, “SQL is the lingua franca of the data professional.” Check current availability and pricing with the seller you choose; O’Reilly’s listing is the reference for edition details.
Primary references
- SQLite, Built-In Scalar SQL Functions: https://www.sqlite.org/lang_corefunc.html
- SQLite, Window Functions: https://www.sqlite.org/windowfunctions.html
- Microsoft Learn, What Are the Microsoft SQL Database Functions? (SQL Server 17): https://learn.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver17
- PostgreSQL 18, Value Expressions: https://www.postgresql.org/docs/current/sql-expressions.html
- PostgreSQL 18, Functions and Operators: https://www.postgresql.org/docs/current/functions.html
- MySQL Reference Manual, Functions and Operators: https://dev.mysql.com/doc/refman/26.7/en/functions.html
- MySQL Reference Manual, Aggregate Function Descriptions: https://dev.mysql.com/doc/refman/26.7/en/aggregate-functions.html
- O’Reilly, SQL Cookbook, 2nd Edition: https://www.oreilly.com/library/view/sql-cookbook-2/9781492077435/
Verify every example against the engine and version you actually run. Function names, defaults, and restrictions change between releases.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →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.
