What is the difference between single-row functions and aggregate functions?
Single-Row Functions vs. Aggregate Functions in MySQL
MySQL functions can be grouped into two major types based on how they operate on data: single-row functions and aggregate functions. They differ in purpose, behavior, and how many rows they process.
- 1
Single-row functions operate on one row at a time.
- 2
For each input row, they return exactly one output value.
- 3
They do not reduce the number of rows in the result set.
- 4
Used in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING clauses.
- 5
Categories include string, numeric, date/time, conversion, and control functions.
- 1
Aggregate functions operate on multiple rows and return a single summarized result.
- 2
Used to perform calculations on groups of rows (e.g., totals, averages).
- 3
Often used with GROUP BY to produce results per group.
- 4
If GROUP BY is not used, the entire table is treated as one group.
- 1
Rows processed: Single-row functions process one row at a time; aggregate functions process multiple rows at once.
- 2
Output: Single-row functions return one result per row; aggregate functions return one result per group.
- 3
Usage: Aggregate functions require GROUP BY if you want results per group; single-row functions do not.
- 4
Effect on rows: Single-row functions do not change the number of rows; aggregate functions reduce rows.
In summary, single-row functions transform individual rows, while aggregate functions summarize multiple rows into a single result.