MySQL Aggregate Functions with Examples

What aggregate functions calculate

An aggregate function produces a summary value from multiple rows. MySQL's common aggregates include COUNT for counting, SUM for totaling, AVG for an arithmetic mean, and MIN and MAX for boundaries. They are useful for reporting, dashboards, financial summaries, and answering questions such as how many orders were paid during a period. Aggregation can operate over a complete filtered result or over groups defined by GROUP BY.

SELECT COUNT(*) AS paid_order_count,
       SUM(total) AS paid_revenue,
       AVG(total) AS average_order_value,
       MIN(total) AS smallest_order,
       MAX(total) AS largest_order
FROM orders
WHERE status = 'paid';

The WHERE clause selects paid order rows before the aggregate calculations. Giving each expression an alias makes output meaningful to reports and application code. If there are no qualifying rows, COUNT returns zero while SUM and AVG can return NULL; consumers should handle those outcomes explicitly rather than assuming every aggregate always produces a number.

COUNT(*) and nullable columns

COUNT(*) counts rows, including rows where individual columns contain NULL. COUNT(column) counts only rows whose specified column is not NULL. This distinction matters when a table stores optional measurements or timestamps. For example, counting all orders differs from counting orders with a known shipment date. Do not select one form by habit; decide whether the question is about records or known values.

Use COUNT(DISTINCT customer_id) when you need the number of unique non-NULL customers represented in the rows. Distinct aggregation can cost more than a simple count because MySQL must identify duplicate values. If an aggregate is slow on a large table, inspect its filters, indexes, grouping, data distribution, and execution plan rather than assuming COUNT is inherently inexpensive.

Aggregate functions with groups

A query without GROUP BY aggregates all rows that survive WHERE into one logical group. Add GROUP BY to produce a summary per key, such as revenue by customer or order count by month. Every selected expression must have a well-defined value per group. With MySQL's default ONLY_FULL_GROUP_BY mode, selecting a non-aggregated column that is not grouped or functionally dependent on the grouping columns is rejected. Do not disable this mode to silence an ambiguous query; decide which value belongs in the result or aggregate it deliberately.

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total) AS lifetime_paid
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY lifetime_paid DESC, customer_id;

Each result row represents one customer group. Sorting by the aggregate alias is readable; the customer key resolves ties. Use HAVING when a condition depends on an aggregate value, while WHERE should filter source rows first. See GROUP BY and HAVING.

Numeric accuracy and business meaning

An average is not always the same as a weighted average. Averaging transaction totals gives each order equal weight; averaging item prices may need quantity as a weight. Financial summaries should use exact DECIMAL values and a clearly specified rounding policy. The database expression's result type and application-driver conversion both matter, so test representative large totals and fractional values. Avoid approximate floating-point values where exact accounting is required. Review numeric data types before designing monetary columns.

Aggregation also changes what a result means. Joining a one-to-many table before computing SUM can duplicate a parent value once per child row. For example, summing order totals after joining each order to several line items inflates totals unless the query is designed around the relationship. Verify cardinality, aggregate at the right level, or use a subquery/CTE to separate calculations. Learn the relationship behavior in MySQL joins.

Performance and readable reports

Large aggregations can read and group many rows. Filter by indexed date or status columns when appropriate, but don't add indexes without considering write cost and selectivity. Select only required group keys and aggregate expressions. If the summary is requested repeatedly, evaluate the query plan and workload before considering caching or a maintained summary table; such a table introduces consistency and refresh requirements.

Clear aliases, explicit NULL behavior, correct grouping, and validated join cardinality make aggregate results trustworthy. Continue with NULL handling, group filtering, and EXPLAIN.