Aggregate Functions in SQL
Introduction
Aggregate functions perform calculations across multiple rows and return one summarized value for the entire filtered set or for each group. They are also called multi row functions or group functions. The five core functions are COUNT, SUM, AVG, MIN, and MAX.
SELECT AVG(salary) AS average_salary
FROM employee;
Aggregate functions answer business questions such as how many customers exist, what revenue was earned, what the average salary is, and which order was smallest or largest. Reliable answers require more than choosing a function: the row scope, result grain, NULL policy, joins, types, and grouping must all be correct.
Core Aggregate Functions
| Function | Purpose | NULL behavior |
|---|---|---|
| COUNT(*) | Counts rows | Counts rows regardless of column NULLs |
| COUNT(expression) | Counts known values | Ignores NULL expression values |
| SUM(expression) | Adds numeric values | Ignores NULL |
| AVG(expression) | Calculates an average | Ignores NULL |
| MIN/MAX | Finds boundaries | Ignores NULL |
SELECT COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employee;
COUNT(*)
COUNT(*) counts every row surviving the FROM, JOIN, and WHERE stages. It is the correct form for questions such as "How many active employees are there?"
SELECT COUNT(*) AS active_employees
FROM employee
WHERE status = 'ACTIVE';
The star expresses row count; it does not force every column into the output. The optimizer can use an appropriate index or table access path. Exact counting over a very large changing dataset can still require substantial work.
COUNT(column)
COUNT(column) counts rows where the selected expression is non-NULL. If 100 employees exist and 30 have a recorded bonus, COUNT(*) returns 100 while COUNT(bonus) returns 30.
SELECT COUNT(*) AS employee_rows,
COUNT(bonus) AS recorded_bonuses
FROM employee;
This distinction is useful for completeness checks, but it is a common source of undercounting when a nullable column is used accidentally.
COUNT(DISTINCT)
COUNT(DISTINCT expression) counts different non-NULL values. It is useful for unique customers, products, sessions, or accounts:
SELECT COUNT(DISTINCT customer_id) AS paying_customers
FROM orders
WHERE status = 'PAID';
Distinct counting may need sorting, hashing, or distributed exchange. Confirm whether uniqueness is based on one column or a composite key, since multi-column syntax varies across databases.
SUM
SUM adds non-NULL numeric values:
SELECT SUM(total_amount) AS settled_revenue
FROM orders
WHERE status = 'SETTLED';
SUM can also aggregate expressions such as quantity times unit price. Ensure the expression uses an exact type and appropriate precision. A NULL expression is ignored, which can silently omit financially incomplete rows unless data quality is validated separately.
AVG
AVG calculates the sum of non-NULL values divided by their non-NULL count:
SELECT AVG(salary) AS average_known_salary
FROM employee;
It does not treat missing salaries as zero. AVG(COALESCE(salary, 0)) changes the numerator and denominator, so it answers a different question. The output type and precision depend on the input and database.
MIN and MAX
MIN and MAX return the smallest and largest non-NULL values. They work with numbers, dates, timestamps, and comparable text under the active collation.
SELECT MIN(created_at) AS earliest_order,
MAX(created_at) AS latest_order
FROM orders
WHERE tenant_id = :tenant_id;
These functions return boundary values, not the associated complete row. Finding the latest order ID, status, and amount requires an ordering or window strategy that resolves ties.
Aggregate Functions without GROUP BY
Without GROUP BY, all filtered rows form one group. A query with several aggregates still returns one summary row:
SELECT COUNT(*) AS order_count,
SUM(total_amount) AS order_amount,
AVG(total_amount) AS average_order,
MIN(total_amount) AS minimum_order,
MAX(total_amount) AS maximum_order
FROM orders
WHERE tenant_id = :tenant_id;
This is suitable for an overall summary. Adding a non-aggregated detail column would make the desired output ambiguous.
GROUP BY and Result Grain
GROUP BY divides filtered rows into groups and returns one result row per distinct grouping key:
SELECT department_id,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employee
GROUP BY department_id;
The grain is one row per department. Adding location_id changes it to one row per department-location combination. Define the grain in plain language before writing the query; many reporting errors are grain errors.
Selected Columns in Grouped Queries
Every selected expression in a grouped query should normally be a grouping expression or an aggregate. Selecting an arbitrary employee name beside a department average is undefined because many employees belong to one department.
Some databases permit relaxed grouping modes, but the chosen detail value may be arbitrary. Use deterministic standard SQL or an explicit window/ranking strategy when a particular associated row is required.
WHERE and HAVING
WHERE filters detail rows before aggregation. HAVING filters completed groups:
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 filters belong in WHERE so unauthorized or irrelevant rows never contribute. The total threshold belongs in HAVING because it depends on the aggregate.
Logical Processing Order
A useful logical model is FROM and JOIN, WHERE, GROUP BY, aggregates, HAVING, SELECT, DISTINCT, ORDER BY, and row limiting. The optimizer can choose a different physical plan while preserving these semantics.
This explains why SUM cannot normally appear directly in WHERE: the aggregate does not exist until after row filtering and grouping. Use HAVING or an outer query around an aggregated subquery.
Ordering Aggregate Results
GROUP BY does not guarantee sorting. Use ORDER BY with a deterministic tie-breaker:
SELECT product_id,
SUM(quantity) AS units_sold
FROM order_line
GROUP BY product_id
ORDER BY units_sold DESC, product_id;
Sorting many groups can require substantial memory or temporary storage. An appropriate index or pre-aggregated structure may help high-volume reports.
Aggregate NULL Behavior
Aggregate functions generally ignore NULL expression values. COUNT(*) is different because it counts rows. If every salary is NULL, COUNT(salary) is zero and SUM, AVG, MIN, and MAX are NULL.
NULL can represent unknown, not applicable, not measured, or missing. Converting it to zero should be a business decision, not a display reflex. Zero is a known numeric value and affects totals and averages.
Empty Result Sets
An ungrouped aggregate over no matching rows still returns one row: COUNT is zero, while SUM, AVG, MIN, and MAX are normally NULL. A grouped query over no matching rows returns no groups and no rows.
SELECT COALESCE(SUM(amount), 0) AS payment_total
FROM payment
WHERE customer_id = :customer_id;
COALESCE is suitable only when the report defines an empty payment set as a zero total.
DISTINCT with Aggregate Functions
DISTINCT inside an aggregate removes duplicate expression values before calculation. SUM(DISTINCT amount) adds each different amount once. It does not remove duplicate transactions by transaction ID.
Two legitimate orders with the same amount collapse together, so SUM(DISTINCT amount) is rarely a correct fix for join duplication. Correct the row population or aggregate at the intended key.
Aggregating Expressions
Aggregates can summarize row-level expressions:
SELECT order_id,
SUM(quantity * unit_price) AS subtotal
FROM order_line
GROUP BY order_id;
If either input is NULL, that line expression may be NULL and ignored. Define whether incomplete lines invalidate the order, represent zero, or should be rejected by constraints. Confirm precision and overflow for large quantities and prices.
Conditional Aggregation
CASE inside aggregates calculates several related metrics in one query:
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,
SUM(CASE WHEN status = 'REFUNDED' THEN total_amount ELSE 0 END) AS refunded_amount
FROM orders
WHERE created_at >= :from_time
AND created_at < :to_time
GROUP BY tenant_id;
The CASE output defines the aggregate population. For ratios and averages, check whether non-qualifying rows should contribute zero or be excluded as NULL.
Aggregates after JOIN
Aggregate correctness depends on the rows produced by joins. Joining an order to three lines repeats the order once per line. Joining lines and payments can multiply independent children and inflate totals.
Write down the grain of every table and the joined result. If one output row should represent a customer, aggregate each many-side to customer grain before combining summaries.
COUNT with LEFT JOIN
COUNT(*) after a LEFT JOIN counts the preserved parent row even when no child matches. Count a non-NULL child key instead:
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;
A customer without orders then receives zero. Ensure the child key is genuinely non-NULL for real child rows.
Pre-Aggregation to Prevent Fan-Out
Summarize independent child tables before joining them:
WITH order_summary AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS order_amount
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id
)
SELECT c.customer_id,
COALESCE(s.order_count, 0) AS order_count,
COALESCE(s.order_amount, 0) AS order_amount
FROM customer c
LEFT JOIN order_summary s
ON s.customer_id = c.customer_id;
Include tenant, date, lifecycle, and authorization filters inside the summary where required. Pre-aggregation improves clarity and can reduce join volume.
MAX Does Not Return the Maximum Row
MAX(created_at) returns one timestamp. It does not automatically return the order ID or status from that row. Joining back on the timestamp can return several rows when ties exist.
Use ROW_NUMBER ordered by timestamp and a unique tie-breaker when exactly one latest row is needed. Use RANK or a join when every tied latest row should be returned. State the tie policy explicitly.
Aggregate Subqueries
An aggregate subquery can calculate a comparison value:
SELECT employee_id, department_id, salary
FROM employee e
WHERE salary > (
SELECT AVG(x.salary)
FROM employee x
WHERE x.department_id = e.department_id
);
This correlated query finds employees above their department average. A grouped CTE joined back to employees may be clearer or faster for large sets. Compare actual plans and verify tenant scope in both layers.
Aggregate of Aggregates
Aggregate functions cannot usually be nested arbitrarily in one grouping level. To calculate an aggregate over group aggregates, use a subquery:
WITH department_average AS (
SELECT department_id, AVG(salary) AS average_salary
FROM employee
GROUP BY department_id
)
SELECT AVG(average_salary) AS average_of_department_averages
FROM department_average;
This is an unweighted average of department averages, not the overall employee average. Metric design must decide which weighting is correct.
GROUP BY versus Window Aggregates
GROUP BY collapses rows to one result per group. A window aggregate preserves detail rows while adding group context:
SELECT employee_id,
department_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) AS department_average
FROM employee;
Choose GROUP BY for summary rows and window functions when details and group metrics must coexist. Window partitions, ordering, frames, and sorting costs require separate review.
Aggregate Performance
Performance depends on input row count, group cardinality, row width, joins, distinct operations, sorting, memory, parallelism, and spills. Hash aggregation stores groups in a hash table; sort or stream aggregation consumes ordered data.
Filter early with sargable WHERE conditions, project only required columns, and avoid function-wrapped date filters. Inspect actual plans for estimate errors, large sorts, spills, and fan-out below the aggregate.
Indexes and Statistics
Indexes aligned with selective filters and grouping or ordering keys can reduce reads and sorting. Accurate statistics help estimate input rows and group counts, which determine join algorithms and memory grants.
COUNT(*) is not automatically a base-table scan, but an exact count can still be expensive. Compare logical reads, CPU, elapsed time, cache state, and concurrent behavior rather than judging one operator name.
Numeric Types, Rounding, and Overflow
SUM can exceed the input type's range, and AVG return precision differs by product. Use exact decimal types for money, cast to a wider type when required, and test maximum expected totals.
Rounding each transaction before SUM can differ from rounding the final SUM. Currency conversion should generally occur at the transaction's applicable rate before values are combined. Document the policy and calculation order.
Aggregate Functions for Data Validation
Aggregates are valuable for profiling and reconciliation. COUNT can detect missing rows, COUNT(column) measures completeness, MIN and MAX expose boundaries, and GROUP BY with HAVING identifies duplicates.
SELECT tenant_id, external_reference, COUNT(*) AS duplicate_count
FROM orders
GROUP BY tenant_id, external_reference
HAVING COUNT(*) > 1;
This query detects duplicate references within a tenant. It is a diagnostic, not a replacement for a UNIQUE constraint when duplicates must be prevented.
Reconciliation Queries
Reliable systems compare independent totals: ledger debits versus credits, order totals versus line totals, or source events versus a materialized summary. Reconciliation should define acceptable timing differences and late-arriving adjustments.
Automated controls should retain the population and keys behind a mismatch, not only the final difference. This makes fan-out, missing filters, and pipeline defects traceable.
Real-World E-Commerce Report
SELECT customer_id,
COUNT(*) AS paid_order_count,
SUM(total_amount) AS lifetime_paid_amount,
AVG(total_amount) AS average_paid_order,
MIN(created_at) AS first_paid_order,
MAX(created_at) AS latest_paid_order
FROM orders
WHERE tenant_id = :tenant_id
AND status = 'PAID'
GROUP BY customer_id
HAVING SUM(total_amount) >= :minimum_lifetime_value
ORDER BY lifetime_paid_amount DESC, customer_id;
The grain is customer. Tenant and status scope apply before aggregation, HAVING applies the lifetime threshold, and ORDER BY makes ranking explicit. The earliest and latest timestamps do not identify complete order rows.
Department and Product Reporting Examples
A department report may count active employees, total payroll, and calculate an average known salary. A product report may count order lines, sum units, and total revenue. Although both use the same functions, their grains and populations differ:
SELECT department_id,
COUNT(*) AS active_employees,
SUM(salary) AS annual_payroll,
AVG(salary) AS average_salary
FROM employee
WHERE status = 'ACTIVE'
GROUP BY department_id;
SELECT product_id,
COUNT(*) AS order_line_count,
SUM(quantity) AS units_sold,
SUM(quantity * unit_price) AS gross_revenue
FROM order_line
WHERE order_status = 'PAID'
GROUP BY product_id;
The employee report must decide whether contractors and NULL salaries belong. The product report must define refunds, discounts, taxes, currency conversion, and canceled lines. Aggregate syntax is straightforward; trustworthy reporting comes from explicit metric boundaries and tests that reconcile summary output with source rows.
Time-Based Reports
Daily and monthly aggregation should use a direct time range in WHERE and a well-defined reporting time zone. Group by year and month together or by a product-specific period-start expression so January from different years is not merged.
For heavy recurring reports, a generated period key, expression index, materialized view, or warehouse may reduce repeated work. Each option adds refresh and consistency responsibilities.
Aggregate Security
Apply tenant, ownership, visibility, and soft-delete conditions before aggregation. Aggregate output can leak sensitive facts even when detail rows are hidden, especially counts for small populations.
Some reports require minimum-group suppression, role-based measures, or privacy techniques. Treat the metric population as an authorization boundary and test cross-tenant rows explicitly.
Metric Definitions and Denominators
Every aggregate metric needs a precise definition. "Average order value" might divide settled revenue by settled orders, submitted revenue by submitted orders, or non-refunded revenue by customers. Each formula can be valid for a different purpose.
Document numerator, denominator, population, exclusions, currency, time zone, NULL handling, and refresh timing. Conditional aggregation should make those choices visible. A denominator based on COUNT(*) differs from COUNT(nullable_value), and a zero returned by CASE differs from an excluded NULL. Metric contracts prevent two teams from publishing conflicting values under the same label.
Ratios and Divide-by-Zero Handling
Ratios often combine aggregate values:
SELECT campaign_id,
SUM(click_count) / NULLIF(SUM(impression_count), 0) AS click_rate
FROM campaign_daily
GROUP BY campaign_id;
NULLIF prevents division by zero, but the result type must preserve decimals. Integer division may return zero for a real fraction, so cast deliberately. A NULL ratio can mean no denominator exists; converting it to zero would claim a measured zero rate. APIs and dashboards should communicate that distinction.
Duplicate Detection and Uniqueness Audits
GROUP BY and HAVING can find duplicate business keys:
SELECT tenant_id,
normalized_email,
COUNT(*) AS duplicate_count,
MIN(customer_id) AS first_customer_id,
MAX(customer_id) AS last_customer_id
FROM customer
GROUP BY tenant_id, normalized_email
HAVING COUNT(*) > 1;
The query identifies suspicious groups and boundary IDs for investigation. It does not safely choose which row to keep or prevent future duplicates. After cleaning data and defining NULL behavior, enforce the intended rule with a UNIQUE constraint or index where possible.
Completeness and Data-Quality Profiling
Aggregates can profile missing and invalid data in one scan:
SELECT COUNT(*) AS all_rows,
COUNT(email) AS rows_with_email,
SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS missing_email,
MIN(created_at) AS earliest_row,
MAX(created_at) AS latest_row
FROM customer
WHERE tenant_id = :tenant_id;
Track these metrics over time to detect ingestion regressions. Validation queries should use the same business boundaries as production, including soft-delete and environment scope. A sudden change may indicate a real process shift rather than bad data, so alerts need expected ranges and operational context.
Dashboards and Snapshot Consistency
A dashboard often displays several aggregate cards that users expect to describe one coherent moment. If each card runs a separate query while data changes, totals can disagree. A single grouped query, one transaction snapshot, or a versioned materialized dataset can provide consistent metrics.
Define whether freshness or cross-metric consistency has priority. Cache keys must include tenant, filters, and metric version. Display the refresh time when values are delayed. Do not combine a live count with a daily summary amount without explaining their different time boundaries.
Cohort and Retention Aggregation
Cohort reports group users by a starting event and summarize later activity. They require careful grain because joining every signup to every event can multiply rows. First derive one cohort key per user, then aggregate distinct active users by cohort and period.
Retention denominators normally use the original eligible cohort, while numerators count members active in a later period. Late events, deleted users, time zones, and identity merges can change historical results. These rules must be versioned so trends remain interpretable after business definitions evolve.
Percentiles and Distribution Metrics
AVG alone can hide skew. Response-time and order-value reporting may need median, percentiles, standard deviation, or histogram buckets. Function names and exact versus approximate behavior vary by database.
Percentiles can be expensive because they often require ordering within groups. Approximate percentile algorithms are useful at large scale when a documented error is acceptable. Report sample count and time range beside distribution metrics so a percentile from a tiny population is not mistaken for a stable signal.
Materialized Aggregates and Summary Tables
Repeated aggregation across years of detail data may be better served by daily summaries, materialized views, or a warehouse. A daily table might store one row per tenant, product, and date, allowing monthly reports to aggregate far fewer rows.
Precomputed metrics introduce refresh and reconciliation responsibilities. Define how late arrivals, corrections, reversals, backfills, and failed refreshes are handled. Store enough additive components to recompute ratios correctly; averaging daily averages without weights usually gives the wrong monthly average.
Additive, Semi-Additive, and Non-Additive Metrics
Revenue is often additive across time and categories. Account balance may be additive across accounts but not across time; summing daily balances over a month is usually meaningless. Ratios and distinct counts are generally non-additive.
Summary-table design must respect these properties. Store sum and count for a weighted average, period-end snapshots for balances, and appropriate sketches or exact keys for distinct counts. A metric that aggregates correctly at one grain may not safely roll up to another.
Database-Specific Aggregate Capabilities
Databases may provide FILTER clauses, ordered-set aggregates, array or JSON aggregation, string aggregation, approximate distinct counts, statistical functions, and grouping sets. These can simplify reports but differ in syntax, ordering, size limits, NULL behavior, and portability.
Use standard functions where portability matters, and isolate vendor-specific features behind tested query modules. Confirm deterministic ordering inside list aggregation and enforce output size limits so one group cannot create an unbounded string or JSON value.
Grouping Sets, Rollup, and Cube
Advanced grouping features can calculate detail groups and subtotals in one statement. ROLLUP commonly adds hierarchical totals, while CUBE can generate combinations of grouping dimensions. GROUPING SETS lets the query specify exact subtotal grains.
Subtotal rows often use NULL in grouping columns, which can be confused with a real NULL key. Product-specific GROUPING functions distinguish generated subtotal NULLs from source NULLs. Label levels clearly and verify that summing displayed rows does not double-count subtotals and details.
Large-Scale Aggregate Performance
For large aggregations, reduce rows and columns before grouping. Partition pruning, columnar storage, clustering, runtime filters, and parallel aggregation can reduce work. High-cardinality groups consume more memory than a small set of categories, even with the same input row count.
Watch hash-table spills, sort spills, skewed parallel workers, data shuffling, and inaccurate group estimates. A single extremely large tenant or category can dominate one partition. Production-like distributions are more informative than uniformly generated test data.
Aggregate Tuning Workflow
- Confirm metric definitions, authorized population, and output grain.
- Verify the detail rows and join cardinality independently.
- Capture baseline rows, CPU, reads, elapsed time, memory, and spills.
- Apply selective sargable filters and remove unnecessary columns.
- Test pre-aggregation, index order, or summary structures one change at a time.
- Reconcile every result before comparing performance.
- Test common, rare, empty, and high-cardinality groups under concurrency.
Do not tune by adding DISTINCT, hints, or indexes blindly. Each change must preserve NULL, duplicate, and empty-set semantics while improving a measured bottleneck.
Production Monitoring and Regression Control
Monitor aggregate query frequency, input rows, output groups, CPU, logical reads, spills, and timeout rates. Data growth or a new grouping dimension can change cost even when SQL text remains unchanged. Plan regressions can also follow statistics changes or database upgrades.
Keep representative reconciliation and performance tests in the release pipeline. Version metric definitions and report changes to downstream consumers. When a dashboard total changes, teams should know whether the cause is data, logic, refresh timing, or a deliberate definition update.
Testing Aggregate Functions
Test zero rows, one row, many rows, duplicates, all NULL, mixed NULLs, negative values, zero, and maximum numeric boundaries. Verify COUNT(*) separately from COUNT(column), and verify AVG's denominator.
Grouped tests should include NULL keys, one and many groups, extra grouping dimensions, and explicit ordering. Join tests need parents with zero, one, and many children plus multiple child tables to expose fan-out. Compare aggregate output with independently summed detail rows.
Common Aggregate Mistakes
- Using COUNT(nullable_column) when every row should count.
- Treating NULL as zero without defining that meaning.
- Placing aggregate conditions in WHERE instead of HAVING.
- Adding too many GROUP BY columns and changing the grain.
- Assuming GROUP BY sorts output.
- Summing values after a one-to-many fan-out.
- Using DISTINCT to hide an incorrect join.
- Using MAX to retrieve a complete latest row.
- Ignoring empty sets, overflow, or rounding rules.
- Aggregating before applying authorization scope.
Aggregate Review Checklist
- Define what one output row represents.
- Verify the exact authorized input population.
- Choose COUNT forms and DISTINCT deliberately.
- Define NULL and empty-set behavior.
- Use WHERE for rows and HAVING for groups.
- Check join cardinality and pre-aggregate where needed.
- Use suitable numeric precision and rounding.
- Order results explicitly and define tie handling.
- Reconcile totals against independent detail queries.
- Inspect actual plans, estimates, memory, and spills.
Interview Questions and Answers
What is an aggregate function? It processes multiple rows and returns one summarized result for a set or group.
What are the main aggregate functions? COUNT, SUM, AVG, MIN, and MAX.
COUNT(*) versus COUNT(column)? COUNT(*) counts rows; COUNT(column) counts non-NULL values.
Does AVG include NULL? No. It normally ignores NULL values.
What does GROUP BY do? It divides filtered rows into groups for independent aggregation.
WHERE versus HAVING? WHERE filters rows before grouping; HAVING filters aggregate groups.
Can aggregates work without GROUP BY? Yes. The entire filtered set is one group.
Why can joins produce wrong totals? One-to-many relationships can multiply rows before aggregation.
Does GROUP BY sort output? No. ORDER BY is required.
Key Takeaway
Aggregate functions convert detailed rows into business metrics. Their correctness depends on row scope, result grain, NULL rules, join cardinality, data types, and security boundaries.
TRUSTWORTHY AGGREGATES = DEFINED METRIC
+ AUTHORIZED ROW SCOPE
+ CORRECT GROUPING GRAIN
+ INTENTIONAL NULL RULES
+ SAFE JOIN CARDINALITY
+ VERIFIED PRECISION
+ RECONCILED RESULTS
Use COUNT, SUM, AVG, MIN, and MAX with explicit grouping and filters, validate results against detail data, and measure execution plans with production-like volumes.