Multi Row Functions in SQL

Introduction

A multi row function processes values from multiple rows and returns one summarized result for a set or group. These functions are commonly called aggregate functions or group functions. COUNT, SUM, AVG, MIN, and MAX are the core examples.

SELECT AVG(salary) AS average_salary
FROM employee;

If four salaries are 50,000, 60,000, 70,000, and 80,000, AVG processes the four values and returns 65,000. The central model is multiple input rows, one aggregate calculation, and one output per group.

Why They Are Called Multi Row Functions

A single row function such as UPPER transforms each name independently and normally returns one result for every row. A multi row function combines many rows into one value. Without GROUP BY, the filtered result is one group. With GROUP BY, each distinct group produces its own aggregate result.

This change in output cardinality is fundamental. Aggregation is not only a calculation; it defines the grain of the result. Before writing a report, state exactly what one output row represents.

Single Row versus Multi Row Functions

CharacteristicSingle rowMulti row
Input scopeOne rowA set or group
OutputOne value per rowOne value per group
ExamplesUPPER, ROUND, CASTCOUNT, SUM, AVG
GROUP BYNot requiredDefines multiple groups

The categories can be composed. ROUND can wrap AVG to round one aggregate result, and SUM can aggregate a row-level CASE expression.

COUNT(*) Counts Rows

COUNT(*) counts rows in the filtered set, regardless of whether individual columns contain NULL:

SELECT COUNT(*) AS employee_count
FROM employee
WHERE status = 'ACTIVE';

The star does not mean every column must be read as output. It expresses row counting, and the optimizer chooses an access method. COUNT(*) can still be expensive across a huge changing table, but it is the correct semantic form when every matching row counts.

COUNT(column) Counts Non-NULL Values

COUNT(column) counts rows where that expression is not NULL:

SELECT COUNT(*) AS all_employees,
       COUNT(bonus) AS employees_with_bonus
FROM employee;

If 100 employees exist and 30 have a recorded bonus, the results are 100 and 30. Use COUNT(column) deliberately to measure known values, not as a substitute for COUNT(*).

COUNT(DISTINCT expression)

COUNT(DISTINCT customer_id) counts different non-NULL customer identifiers. Duplicate customer IDs contribute once, and NULL is normally excluded.

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE status = 'PAID';

Distinct counting may require sorting, hashing, or specialized algorithms. Multi-column distinct syntax differs by database. Confirm the intended uniqueness grain before adding DISTINCT.

SUM

SUM adds non-NULL numeric expression values across the group:

SELECT SUM(total_amount) AS paid_amount
FROM orders
WHERE status = 'PAID';

NULL amounts are ignored; they are not automatically converted to zero. If no rows match, SUM normally returns NULL. Use COALESCE only when the business defines an empty set as a zero total.

AVG

AVG calculates the average of non-NULL numeric values. Conceptually it divides the sum of known values by their non-NULL count.

SELECT AVG(salary) AS average_known_salary
FROM employee;

AVG does not treat NULL as zero. AVG(COALESCE(salary, 0)) changes both numerator and denominator and answers a different question. Choose the form based on the meaning of missing data.

MIN and MAX

MIN returns the smallest non-NULL value and MAX returns the largest. They work with numbers, dates, timestamps, and comparable text values under the database collation.

SELECT MIN(created_at) AS first_order_time,
       MAX(created_at) AS latest_order_time
FROM orders
WHERE customer_id = :customer_id;

MIN or MAX returns only the boundary value, not the entire row associated with it. Retrieving the latest order requires an ordering, window function, or join that handles ties explicitly.

Aggregates without GROUP BY

Without GROUP BY, all filtered rows form one group:

SELECT COUNT(*) AS order_count,
       SUM(total_amount) AS total_amount,
       AVG(total_amount) AS average_amount,
       MIN(total_amount) AS minimum_amount,
       MAX(total_amount) AS maximum_amount
FROM orders
WHERE tenant_id = :tenant_id;

The query returns one row even when no source rows match. COUNT is zero; SUM, AVG, MIN, and MAX are normally NULL.

GROUP BY Creates Groups

GROUP BY partitions filtered rows by one or more expressions, then calculates aggregates independently for each group:

SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employee
GROUP BY department_id;

One output row represents one department. Adding location_id to GROUP BY changes the grain to one row per department and location. Extra grouping columns can silently fragment metrics.

The GROUP BY Selection Rule

In a grouped query, selected expressions normally must either be aggregated or identify the group. Selecting an unrelated detail column is ambiguous because several source values may exist in one group.

SELECT department_id,
       COUNT(*) AS employee_count,
       MAX(salary) AS highest_salary
FROM employee
GROUP BY department_id;

Some products permit relaxed grouping modes, but the chosen detail value may be arbitrary. Write deterministic standard grouped queries rather than depending on permissive behavior.

WHERE and HAVING

WHERE filters source rows before grouping. HAVING filters aggregate groups after grouping:

SELECT customer_id,
       COUNT(*) AS paid_orders,
       SUM(total_amount) AS paid_amount
FROM orders
WHERE tenant_id = :tenant_id
  AND status = 'PAID'
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;

Tenant and status belong in WHERE because they determine which rows may contribute. The aggregate threshold belongs in HAVING. Using HAVING for ordinary row filters can process unnecessary input and obscure intent.

Logical Processing Model

A useful conceptual sequence is FROM and JOIN, WHERE, GROUP BY, aggregate calculation, HAVING, SELECT, DISTINCT, ORDER BY, and row limiting. The optimizer may execute a different physical plan while preserving the same semantics.

This model explains why aggregate aliases may not be visible in WHERE and why WHERE cannot directly test SUM. The aggregate does not logically exist until after grouping.

Ordering Aggregate Results

GROUP BY does not guarantee output order. Use ORDER BY explicitly:

SELECT department_id,
       AVG(salary) AS average_salary
FROM employee
GROUP BY department_id
ORDER BY average_salary DESC, department_id;

A deterministic tie-breaker makes report and pagination order stable. Sorting many groups can require memory and temporary storage, so inspect plans for large cardinalities.

DISTINCT inside Aggregates

DISTINCT removes duplicate expression values before the aggregate. SUM(DISTINCT amount) adds each different amount once, not each business transaction once. Two legitimate transactions with the same amount collapse together.

Use DISTINCT only when value-level uniqueness is the requirement. It is not a general repair for duplicate rows produced by a wrong join. Establish the correct row grain before aggregating.

NULL and Empty Sets

COUNT(*) counts rows. COUNT(expression), SUM, AVG, MIN, and MAX generally ignore NULL expression values. When every value is NULL, COUNT(expression) is zero and the other aggregates are NULL.

An ungrouped aggregate over an empty result still returns one row. A grouped query over no rows returns no groups and therefore no rows. Applications must distinguish no group, a group with only NULL values, and a valid zero.

Conditional Aggregation

CASE inside an aggregate calculates related metrics from one grouped population:

SELECT tenant_id,
       COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) AS paid_orders,
       SUM(CASE WHEN status = 'PAID' THEN total_amount ELSE 0 END) AS paid_amount
FROM orders
WHERE created_at >= :from_time
  AND created_at < :to_time
GROUP BY tenant_id;

The ELSE branch matters. Zero participates in SUM and AVG, while NULL is ignored. Design each CASE expression according to the metric's population and denominator.

Aggregates after JOIN

Joins establish the rows that aggregates receive. A one-to-many join repeats parent values for every child. Summing a parent amount after such a join can multiply money.

Before aggregating, write down the grain of each table, each relationship, and the joined result. If an order has three lines and two payments, joining both child tables directly can produce six rows for one order. Aggregates over that result are usually wrong.

COUNT with LEFT JOIN

After a LEFT JOIN, COUNT(*) counts the preserved parent row even when no child exists. Count a non-NULL child key to count matching children:

SELECT c.customer_id,
       COUNT(o.order_id) AS order_count
FROM customer c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Customers with no orders receive zero because the NULL placeholder child key is not counted.

Avoiding Join Fan-Out

Aggregate each many-side to the required parent grain before joining multiple children:

WITH order_totals AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS order_amount
  FROM orders
  GROUP BY customer_id
), payment_totals AS (
  SELECT customer_id, SUM(amount) AS payment_amount
  FROM payment
  GROUP BY customer_id
)
SELECT c.customer_id,
       COALESCE(o.order_count, 0) AS order_count,
       COALESCE(o.order_amount, 0) AS order_amount,
       COALESCE(p.payment_amount, 0) AS payment_amount
FROM customer c
LEFT JOIN order_totals o ON o.customer_id = c.customer_id
LEFT JOIN payment_totals p ON p.customer_id = c.customer_id;

Pre-aggregation creates at most one row per customer in each child summary. Validate that child filters and tenant scope are applied within each summary.

Result Grain

Grain describes what one output row represents. A monthly sales report may have one row per tenant and calendar month. A department report may have one row per department. The SELECT list, GROUP BY, joins, and aggregate definitions must all agree with that grain.

State the grain in a comment or report specification before coding. Many aggregate defects are not arithmetic errors; they are grain errors caused by extra grouping columns or fan-out.

MIN/MAX and Associated Rows

MAX(created_at) returns the latest timestamp but not the order ID, status, or amount from that row. Selecting an arbitrary detail column beside MAX does not retrieve the latest row reliably.

Use ROW_NUMBER ordered by timestamp and a tie-breaker, or join back on the maximum value while handling ties. Define whether multiple records sharing the latest timestamp should all be returned.

Numeric Precision and Overflow

SUM can exceed the range of its input type, and AVG return precision varies by database. Currency totals should use suitable exact numeric types. Cast deliberately when the database does not widen the aggregate enough.

Round at the stage required by the business. Summing individually rounded values can differ from rounding one final total. Test maximum row counts, extreme values, negative adjustments, and mixed currencies.

Aggregating Expressions

An aggregate can summarize a row-level expression:

SELECT order_id,
       SUM(quantity * unit_price) AS line_subtotal
FROM order_line
GROUP BY order_id;

If either quantity or unit_price is NULL, that row expression may be NULL and be ignored by SUM. Decide whether missing components invalidate the total, represent zero, or should trigger a data-quality error. Financial missingness must not be hidden accidentally.

Nested Aggregate and Scalar Functions

Scalar functions can wrap an aggregate result:

SELECT ROUND(AVG(salary), 2) AS rounded_average
FROM employee;

Arbitrary aggregate nesting such as SUM(AVG(salary)) is normally invalid in one grouping level. To aggregate aggregate results, create the first grouping in a subquery or CTE, then aggregate those rows at the outer level. Confirm whether the intended result is a weighted or unweighted average.

Multi Row versus Window Functions

A GROUP BY aggregate collapses rows to groups. A window aggregate computes across a partition while preserving each detail row:

SELECT employee_id,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS department_average
FROM employee;

Use GROUP BY when the report needs one row per group. Use a window function when each detail row must remain alongside group context. Window frames and ordering introduce additional semantics and cost.

Aggregate Subqueries

An uncorrelated aggregate subquery can calculate one value used by an outer query. A correlated aggregate subquery recalculates logically for each outer row:

SELECT e.employee_id, e.salary
FROM employee e
WHERE e.salary > (
  SELECT AVG(x.salary)
  FROM employee x
  WHERE x.department_id = e.department_id
);

The optimizer may transform this efficiently, but correlated aggregation can be expensive. A grouped CTE joined back to employees can make the relationship and plan clearer for large data.

Aggregation Security

Apply tenant, ownership, visibility, soft-delete, and authorization filters before aggregation. A final grouped row can leak information even when individual detail rows are hidden. Counts for very small groups may reveal sensitive presence.

Reporting systems may require minimum-group suppression, noise, or role-specific measures. Index security-scope columns as part of real query patterns, but never remove authorization predicates for performance.

Aggregate Performance

Aggregation cost depends on input rows, number of groups, row width, joins, distinct processing, sorting, memory, and parallelism. Hash aggregation builds group state in memory; sort-based aggregation orders rows; index-ordered input may allow streaming aggregation.

Filter early with sargable WHERE predicates, select only required columns, and avoid unbounded analytics on transactional systems. Inspect actual plans for spills, estimate errors, repeated scans, excessive exchanges, and join fan-out.

Indexes and Statistics

An index aligned with selective filters and grouping or ordering keys can reduce reads and sorting. COUNT(*) is not guaranteed to scan the entire base table; the optimizer can choose a narrower structure. It still must produce a transactionally correct count under the database's rules.

Statistics determine estimated input rows and group counts. Stale estimates can cause poor memory grants, spills, and wrong join strategies. Maintain statistics and compare estimated with actual rows using representative parameter values.

Pre-Aggregation and Materialized Summaries

For repeated expensive reports, summary tables or materialized views can store daily or monthly aggregates. They trade query-time work for refresh, storage, and consistency complexity.

Define refresh frequency, late-arriving data, corrections, reconciliation, ownership, and failure recovery. A summary must preserve tenant security and metric definitions. Use approximate aggregates only where a documented error bound is acceptable.

Real-World Sales Report

SELECT tenant_id,
       EXTRACT(YEAR FROM paid_at) AS paid_year,
       EXTRACT(MONTH FROM paid_at) AS paid_month,
       COUNT(*) AS payment_count,
       COUNT(DISTINCT customer_id) AS customer_count,
       SUM(amount) AS paid_amount,
       AVG(amount) AS average_payment,
       MIN(amount) AS minimum_payment,
       MAX(amount) AS maximum_payment
FROM payment
WHERE status = 'SETTLED'
  AND paid_at >= :from_time
  AND paid_at < :to_time
GROUP BY tenant_id,
         EXTRACT(YEAR FROM paid_at),
         EXTRACT(MONTH FROM paid_at)
ORDER BY tenant_id, paid_year, paid_month;

The grain is tenant-month. The WHERE clause bounds source rows, and each aggregate answers a different question. For a high-volume workload, generated period keys or expression indexes may avoid repeated extraction.

Conditional Counts and Denominators

Conditional aggregation can count a subset by returning one for qualifying rows and zero for others:

SELECT department_id,
       COUNT(*) AS all_employees,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_employees,
       SUM(CASE WHEN status = 'ON_LEAVE' THEN 1 ELSE 0 END) AS leave_employees
FROM employee
GROUP BY department_id;

An alternative is COUNT(CASE WHEN condition THEN 1 END), which counts non-NULL values. Both patterns can be correct, but the ELSE expression controls the population. For ratios, define the denominator explicitly. Active employees divided by all employees answers a different question from active employees divided by employees with a known status.

Some databases support a FILTER clause on aggregates, which can express the same intent more directly. Portability and plan behavior vary, so use the form that is clear and supported by the target system.

NULL Grouping Keys

GROUP BY places NULL grouping keys into a group representing rows whose key is unknown or missing. That does not mean the NULL values become equal in every SQL context; it is the grouping behavior required for aggregation.

SELECT department_id,
       COUNT(*) AS employee_count
FROM employee
GROUP BY department_id;

Employees without a department produce a NULL department group. A report can label it with COALESCE, but that changes only presentation. If a real department code also uses the same label, the output can become ambiguous. Keep the underlying key available or choose a distinct display value.

Time-Based Grouping

Monthly and daily reports often group timestamps by a derived period. Extracting year and month separately avoids merging the same month across years, while a database-specific date-truncation function can produce a period start value.

SELECT EXTRACT(YEAR FROM created_at) AS order_year,
       EXTRACT(MONTH FROM created_at) AS order_month,
       COUNT(*) AS order_count,
       SUM(total_amount) AS order_amount
FROM orders
WHERE created_at >= :from_time
  AND created_at < :to_time
GROUP BY EXTRACT(YEAR FROM created_at),
         EXTRACT(MONTH FROM created_at);

Use direct timestamp ranges in WHERE for sargability and partition pruning. Define the reporting time zone before deriving periods. A transaction near midnight can belong to different dates in UTC and local business time, and daylight-saving changes can alter day lengths.

Banking and Signed Transactions

Banking ledgers often store signed amounts, where credits and debits contribute positive and negative values. A simple SUM returns the net movement, while separate conditional sums expose inflow and outflow:

SELECT account_id,
       SUM(amount) AS net_change,
       SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS credits,
       SUM(CASE WHEN amount < 0 THEN ABS(amount) ELSE 0 END) AS debits
FROM ledger_entry
WHERE posted_at >= :from_time
  AND posted_at < :to_time
GROUP BY account_id;

The metric definition must specify pending, reversed, disputed, and multi-currency entries. Converting currencies after aggregation can be wrong when exchange rates vary per transaction. Normalize each row with its applicable rate or group by currency before combining values.

Inventory Aggregation

Inventory reports may sum quantity changes by warehouse and product. Snapshot quantity and movement quantity are different grains; joining both without care can duplicate totals.

SELECT warehouse_id,
       product_id,
       SUM(quantity_change) AS net_quantity_change,
       MIN(event_time) AS first_event,
       MAX(event_time) AS latest_event
FROM inventory_movement
WHERE event_time >= :from_time
  AND event_time < :to_time
GROUP BY warehouse_id, product_id;

The latest event time does not reveal the quantity after that event. Use a window function or a correctly maintained snapshot to retrieve associated row state. Reconciliation tests should compare opening balance plus movements with closing balance.

Aggregate Validation in APIs

APIs that return counts and totals should define metric names, units, currency, time zone, filtering scope, and freshness. A field named total is ambiguous: it might mean row count, gross amount, net amount, or a page subtotal.

Validate the aggregate independently from pagination. The items on one response page rarely sum to the full dataset total. Apply identical security and filter predicates to the detail and summary queries, or calculate them from one shared query structure. Contract tests should cover zero results, NULL metrics, rounding, and tenant isolation.

Report Contracts and Reconciliation

A production aggregate should have a written contract describing grain, source tables, included and excluded states, time-zone boundaries, currency handling, NULL rules, rounding, and refresh timing. Without that contract, two correct SQL queries can answer different interpretations of the same business label.

Reconcile important reports against independent controls. Compare a daily total with the sum of its hourly groups, a tenant total with authorized detail rows, and a summary table with its source events. Record expected differences caused by late arrivals, reversals, or refresh delay. Automated reconciliation detects fan-out, missed filters, and pipeline defects before users rely on incorrect metrics.

Aggregate Before Joining

Pre-aggregation is especially useful when a report combines several one-to-many relationships. Aggregate orders to customer grain, payments to customer grain, and support tickets to customer grain before joining the summaries. This prevents the Cartesian multiplication of independent child rows.

Push the correct filters into each child summary. If the final report is for one tenant and one date range, each pre-aggregation must enforce those boundaries. Joining unscoped summaries and filtering only the parent afterward can produce incorrect totals or security exposure.

Pre-aggregation may also reduce join volume dramatically. However, the optimizer can sometimes derive a good equivalent plan from the original query. Prefer the form that makes grain and correctness visible, then measure both plans.

Exact and Approximate Aggregation

Exact COUNT(DISTINCT) across very large datasets can require substantial memory, sorting, or distributed communication. Some analytical databases offer approximate distinct counting and percentile functions with documented error bounds.

Approximation can be appropriate for dashboards, trend analysis, or capacity planning where a small known error is acceptable. It is not appropriate for billing, compliance, inventory, or any contract requiring exact values. Label approximate metrics clearly and test their statistical behavior at expected cardinalities.

OLTP versus Analytical Aggregation

Transactional databases are optimized for short reads and writes, while analytical systems are designed for scans and grouping across large histories. Running an unbounded multi-year report on a busy OLTP primary can compete with customer transactions for CPU, I/O, memory, and locks.

Use bounded time ranges, read replicas where consistency permits, warehouses, summary tables, or scheduled materialization for heavy analytics. Data movement introduces freshness and reconciliation concerns, so the architecture must document the source of truth and acceptable delay.

Understanding Aggregate Execution Plans

A hash aggregate usually builds a hash table keyed by grouping columns. It works well when group count fits memory, but poor cardinality estimates can cause spills. A sort aggregate orders input by grouping keys and can be efficient when data already arrives in index order. A stream aggregate consumes ordered rows with limited state.

Inspect actual versus estimated input rows and groups, memory grants, temporary storage, parallel exchanges, and time spent below the aggregate. A slow aggregate operator may be a symptom of join fan-out or late filtering rather than an inherently expensive SUM.

Test common and rare parameters because group cardinality can vary dramatically. Statistics on filter and group columns help the optimizer choose memory and algorithms. One elapsed-time sample is not enough; compare logical reads, CPU, spills, and concurrency.

Performance Tuning Workflow

  1. Confirm the metric definitions and output grain.
  2. Run a detail query that exposes the exact contributing rows.
  3. Verify joins do not multiply that population.
  4. Apply selective, type-correct filters before grouping.
  5. Capture the actual plan and baseline resource metrics.
  6. Change one index, rewrite, or pre-aggregation at a time.
  7. Reconcile output totals before comparing performance.
  8. Test production-like distributions, concurrency, and empty sets.

This sequence prevents tuning from hiding a correctness defect. An optimization is successful only when it preserves every metric and improves the measured workload without unacceptable write or maintenance cost.

Testing Multi Row Functions

Test zero rows, one row, many rows, duplicate values, all NULL, mixed NULL and non-NULL, negative values, zero, and maximum numeric boundaries. COUNT tests must distinguish row count from non-NULL count. AVG tests must verify the denominator.

GROUP BY tests need one group, many groups, NULL grouping keys, collations, and added grouping dimensions. Join tests should include parents with zero, one, and several children plus multiple child tables to expose fan-out. Compare detail-level sums with grouped results independently.

Common Aggregate Mistakes

  • Using COUNT(column) when every row should count.
  • Using COUNT(*) after LEFT JOIN to count children.
  • Treating NULL as zero without a business rule.
  • Filtering source rows with HAVING instead of WHERE.
  • Adding an extra GROUP BY column that changes the grain.
  • Summing parent values after a one-to-many join.
  • Using DISTINCT to hide fan-out or duplicate join rows.
  • Using MAX to retrieve a complete latest row.
  • Ignoring empty-set results, precision, or overflow.
  • Assuming GROUP BY sorts the output.

Practical Review Checklist

  1. State the output grain before writing the query.
  2. Apply tenant and authorization scope before aggregation.
  3. Choose COUNT(*), COUNT(column), and DISTINCT deliberately.
  4. Define NULL and empty-set behavior for every metric.
  5. Use WHERE for row filters and HAVING for group filters.
  6. Verify join cardinality and pre-aggregate many-side tables.
  7. Use exact numeric types and test overflow and rounding.
  8. Order results explicitly and handle ties.
  9. Compare detail and aggregate totals with independent tests.
  10. Inspect actual plans, estimates, memory, and spills.

Interview Questions and Answers

What is a multi row function? It processes a set of rows and returns one summarized value for the set or group.

What is another name? Aggregate function or group function.

COUNT(*) versus COUNT(column)? COUNT(*) counts rows; COUNT(column) counts non-NULL expression values.

Does AVG treat NULL as zero? No. It normally ignores NULL values.

What does GROUP BY do? It divides filtered rows into groups and produces aggregate results per group.

WHERE versus HAVING? WHERE filters rows before grouping; HAVING filters groups after aggregation.

Does GROUP BY guarantee sorting? No. Use ORDER BY.

Why can joins make aggregates wrong? One-to-many joins can multiply values before aggregation.

GROUP BY versus window aggregate? GROUP BY collapses rows; a window aggregate preserves detail rows.

Key Takeaway

Multi row functions summarize sets of rows. Correct aggregation depends on the input population, result grain, NULL policy, join cardinality, grouping keys, and numeric type as much as the function name itself.

RELIABLE AGGREGATION = DEFINED GRAIN
                     + CORRECT ROW SCOPE
                     + INTENTIONAL NULL RULES
                     + SAFE JOIN CARDINALITY
                     + APPROPRIATE NUMERIC TYPES
                     + VERIFIED EMPTY SETS
                     + MEASURED EXECUTION PLANS

Use COUNT, SUM, AVG, MIN, and MAX with explicit grouping and security scope, validate totals against detail data, and measure performance with production-like distributions.