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.
Scenario Questions
0-2 years experience
- 1We need to list each employee's salary and also show the total payroll in the same result. Which type of function would you use for the salary column and which for the total, and why?
- 2If you run SELECT MAX(salary) FROM employees; is MAX a single‑row or an aggregate function? What changes if you also select employee_name without adding GROUP BY?
- 3How does MySQL treat CONCAT() compared to SUM() when you add a GROUP BY clause on department_id?
2-5 years experience
- 1You added a DATE_FORMAT() column to a query that already groups by region, and the result started returning duplicate rows. Explain why this happened and how to correct it.
- 2During a bug fix, moving COUNT(*) from a subquery to the outer SELECT changed the result set. What difference between aggregate and scalar functions caused this behavior?
- 3Design a report that shows each customer's average order value and also includes that customer's most recent order date. How would you combine single‑row and aggregate functions without breaking grouping rules?
5-8 years experience
- 1Our nightly analytics job aggregates millions of rows. We want to replace a custom row‑by‑row calculation with a built‑in aggregate, but some logic still needs per‑row evaluation. How do you decide when to embed a scalar function inside an aggregation versus using a derived table, considering performance and correctness?
- 2A legacy view mixes SUM() with IFNULL() on the same expression, causing MySQL to create a temporary table and slow down. Explain how the interaction between aggregate and scalar functions leads to this, and propose a redesign.
- 3When scaling a sharded MySQL cluster, we need a global total across shards. Discuss the challenges of using a single aggregate function versus pulling per‑shard scalar results and aggregating in application code.
8+ years experience
- 1We are migrating a monolithic MySQL reporting database to a distributed analytics platform. Part of the migration requires rewriting queries that currently mix aggregate functions with non‑aggregated columns. What architectural guidelines would you set to avoid hidden grouping bugs and keep future queries maintainable?
- 2Our organization wants to enforce query patterns that prevent misuse of aggregate functions causing full table scans. As a staff engineer, how would you define policies, tooling, and code‑review checks around the distinction between single‑row and aggregate functions across multiple services?
Follow-up Questions
- Can you show a query that would error because a scalar column is selected alongside an aggregate without GROUP BY?
- What impact does MySQL have on performance when it must create a temporary table for aggregation?
- How would you verify that a fix to a grouping issue doesn’t break other parts of the report?