MySQL GROUP BY and HAVING

How grouping changes a query

GROUP BY partitions rows that share one or more grouping values. Aggregate functions then calculate a result for each group. Instead of returning one row for every order, a report can return one row per customer, month, product, or status. Choose grouping columns that describe the unit of analysis you want. Grouping by both customer and order, for example, no longer produces one summary per customer because order_id makes each group more specific.

SQL distinguishes filtering individual source rows from filtering calculated groups. WHERE is applied before grouping and removes rows that should not contribute to any aggregate. HAVING is applied to grouped results and is appropriate for conditions such as requiring at least five orders or a total above a threshold. Filtering early with WHERE can reduce work and makes the population being summarized explicit.

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

This query groups only paid orders, calculates a count and total per customer, and retains groups whose aggregate reaches 500. The final ORDER BY sorts the resulting groups; the customer identifier makes ties deterministic. An alias can make the output easier to read, but using the full aggregate expression in HAVING is portable across SQL modes and makes the condition explicit.

Select valid expressions for each group

Every SELECT expression in a grouped query must have a meaningful value for each group. Aggregate functions produce one value from group rows. A grouping column has the same value for every row in the group. Other columns must be functionally dependent on grouping columns or MySQL's ONLY_FULL_GROUP_BY validation may reject the query. If the mode is disabled, an ungrouped column may come from an arbitrary row, which can return a misleading answer. Fix the grouping or define an aggregate instead of relying on an accidental value.

NULL grouping values form a group together, so a report can include a group whose key is unknown. Decide whether that group should be shown, labeled, or excluded. For counts, distinguish COUNT(*) from COUNT(column), since the latter ignores NULL inputs. Read NULL and aggregate behavior when reports contain optional attributes.

Multiple grouping columns and expressions

Grouping by multiple columns creates a group for each distinct combination. A monthly sales report might group by year and month, or by a date expression and region. Ensure the expression reflects the intended calendar and time zone. Formatting timestamps into strings may be convenient for display but can add computation and make indexing less direct. For large reporting workloads, consider grouping on a well-defined date dimension or using an appropriate range filter before aggregation.

Do not confuse GROUP BY with ORDER BY. Grouping controls which rows feed each aggregate; it does not promise that output groups are returned in a desired order. Add ORDER BY explicitly for user-facing output. Similarly, HAVING does not guarantee efficient filtering when the same condition could be applied before grouping. Use WHERE for source-row conditions and reserve HAVING for group-level requirements.

Avoid inflated aggregates from joins

A join can multiply rows before grouping. If an order has three line items, joining orders to order_items creates three joined rows for that order. Summing the order's total at that stage counts its total three times. Aggregate at the right grain, join pre-aggregated subqueries when necessary, or sum a line-level amount if that is the intended metric. Check relationships and result cardinality with small sample data. See join semantics and subqueries.

Performance and verification

Grouping may require sorting, temporary work, or scanning many rows, depending on the plan and available indexes. An index can help some filters and grouping patterns but may not eliminate all aggregate cost. Avoid guessing from a small table. Run EXPLAIN, test with representative data volumes, and monitor the query's frequency and impact. Large summary jobs may need scheduling, pagination, or a separately maintained reporting model, each with consistency trade-offs.

A trustworthy grouped query makes its grain, input filters, aggregate expressions, and final ordering explicit. Test boundary groups, NULL keys, duplicate join paths, and no-row results before using a summary for business decisions. Continue with aggregate functions, indexes, and query plans.