Aggregation, GROUP BY, HAVING and Query Reasoning

1. From Detail Rows to Summary Values

An aggregate function consumes a collection of rows and produces one summary value. Common aggregates answer questions such as how many orders exist, what their total value is and what the highest salary is.

SELECT COUNT(*) AS order_count,
       SUM(total_amount) AS total_sales,
       AVG(total_amount) AS mean_order,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM order_header;

Without GROUP BY, the filtered input is treated as one group and the query normally returns one summary row.

2. COUNT Variants

COUNT(*) counts input rows regardless of NULL values:

SELECT COUNT(*) FROM employee;

COUNT(expression) counts rows for which the expression is non-NULL:

SELECT COUNT(salary) FROM employee;

The difference counts rows with NULL salary:

COUNT(*) - COUNT(salary)

COUNT(DISTINCT salary) counts distinct non-NULL salary values. It does not count employees and should not be used when the question asks for people rather than salary levels.

3. SUM and AVG

SUM adds non-NULL expression values. AVG computes their arithmetic mean, also ignoring NULL:

SELECT SUM(salary), AVG(salary)
FROM employee;

AVG(salary) equals SUM(salary) divided by COUNT(salary), not COUNT(*), when salary can be NULL. Treating an absent salary as zero requires an explicit domain decision:

AVG(COALESCE(salary, 0))

This changes the population and result. NULL should not be replaced merely to avoid understanding it.

Numeric type and scale affect aggregate precision. Money should generally use exact DECIMAL columns and appropriate result handling.

4. MIN and MAX

MIN and MAX return the smallest and largest non-NULL values according to type and collation:

SELECT MIN(hired_on), MAX(hired_on)
FROM employee;

They work with dates and text as well as numbers. A maximum name is the greatest name under the selected collation, not the row containing the greatest salary.

To retrieve a complete row associated with an extreme value, use an ordered query, subquery, join or window-function design. Selecting MAX(salary) and an unrelated employee_name does not establish that the name belongs to the maximum salary.

5. Empty Input

For an ungrouped aggregate query over no input rows, COUNT returns 0. SUM, AVG, MIN and MAX ordinarily return NULL because no non-NULL value exists from which to derive a result.

SELECT COALESCE(SUM(amount), 0)
FROM payment
WHERE customer_id = 999;

COALESCE is suitable when the domain defines the total of no payments as zero. Preserve NULL when absence should remain distinguishable from a calculated zero.

6. GROUP BY

GROUP BY partitions filtered rows into groups with equal grouping expressions:

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

The result contains one row per department_id group. NULL grouping values normally form one group together.

Grouping does not inherently sort output. Use ORDER BY when order matters.

7. Grouping by Several Columns

Several expressions define groups by their complete combination:

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

An employee belongs to the group identified by both values. Grouping by department alone answers a different question.

Use stable identifiers. Grouping only by a nonunique department name can merge different departments that share a label. Group by department_id and include the name where it is functionally determined and accepted by strict SQL rules.

8. Grouped Select Lists

After grouping, each output row represents an entire group. A selected expression must normally be:

a grouping expression;
an aggregate over the group; or
functionally dependent on grouped keys where the database can establish that dependency.

Selecting an arbitrary ungrouped employee_name has no single defined value for a department group. MySQL's ONLY_FULL_GROUP_BY mode rejects nondeterministic grouped queries and should remain enabled.

If the desired value is “any name,” state why and use an intentional operation. Usually the query question has not been defined correctly.

9. WHERE Before Grouping

WHERE filters detail rows before groups are formed:

SELECT department_id,
       COUNT(*) AS active_count,
       AVG(salary) AS mean_salary
FROM employee
WHERE status = 'ACTIVE'
GROUP BY department_id;

The population is active employees. Inactive employees do not participate in counts or averages.

Use WHERE for row-level conditions that do not require aggregates. Early filtering also reduces the rows that later stages must process.

10. HAVING After Grouping

HAVING filters formed groups:

SELECT department_id,
       COUNT(*) AS active_count,
       AVG(salary) AS mean_salary
FROM employee
WHERE status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) >= 5;

Only groups containing at least five active employees remain. WHERE cannot directly test COUNT(*) because aggregation has not occurred at that logical stage.

MySQL may allow selected aliases in HAVING:

HAVING active_count >= 5

Using the aggregate expression directly is more portable.

11. WHERE and HAVING Are Not Interchangeable

Consider:

WHERE salary >= 50000

This removes lower salaries before calculating an average.

HAVING AVG(salary) >= 50000

This calculates the average over all eligible salaries and then removes groups with a lower average.

Moving a condition between WHERE and HAVING can change both group membership and aggregate values. Put each condition at the stage matching its meaning.

12. Logical Processing Order

For a grouped query, a useful conceptual order is:

FROM and JOIN form source rows;
WHERE filters those rows;
GROUP BY partitions the survivors;
aggregate functions summarize each group;
HAVING filters groups;
SELECT produces output expressions;
DISTINCT removes duplicate result rows;
ORDER BY orders them;
LIMIT restricts output.

This order describes query meaning, not a mandatory physical algorithm. The optimizer may push safe predicates, choose indexes or reorder joins while preserving semantics.

13. Conditional Aggregation

CASE inside an aggregate computes several metrics in one grouping pass:

SELECT department_id,
       COUNT(*) AS total_count,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END)
           AS active_count,
       SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END)
           AS inactive_count
FROM employee
GROUP BY department_id;

The CASE returns 1 or 0 for each row and SUM totals the selected category.

For conditional amounts:

SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END)

Decide whether an unmatched group should yield zero or NULL and choose ELSE accordingly.

14. Distinct Aggregation

DISTINCT can apply inside an aggregate:

COUNT(DISTINCT customer_id)

This counts different non-NULL customers rather than orders.

SUM(DISTINCT amount)

adds each distinct amount once, which is rarely a correct way to repair duplicate rows from a join. Two legitimate payments may have the same amount. Fix join cardinality instead of discarding equal values.

15. Aggregation After Joins

Joins form the input before aggregation. In a one-to-many join, a parent row appears once per matching child.

Suppose ORDER_HEADER.total_amount is 1000 and the order has four lines. Joining header to lines produces four rows containing the same 1000. SUM(order_header.total_amount) then produces 4000.

This is not an aggregate defect. The query supplied a multiplied population. Aggregate each side at its natural grain before joining, sum child line amounts instead or query parent totals without the child join.

16. Grain

The grain of a relation or result states what one row represents. ORDER_HEADER has one row per order. ORDER_LINE has one row per order line. A join of them has one row per matching order-line relationship.

Before writing aggregates, state the intended result grain:

one row for the whole system;
one row per department;
one row per customer per month;
one row per product category.

Then identify the input grain. Many inflated reports come from grouping a result whose row meaning was never stated.

17. Pre-Aggregation

Aggregate a many-side relation before joining when the parent result requires one row per parent:

SELECT o.order_id,
       o.customer_id,
       COALESCE(l.line_total, 0) AS line_total
FROM order_header AS o
LEFT JOIN (
    SELECT order_id,
           SUM(quantity * unit_price) AS line_total
    FROM order_line
    GROUP BY order_id
) AS l
  ON l.order_id = o.order_id;

The derived table has one row per order_id, so joining it to an order no longer multiplies parent rows.

18. Outer Joins and Counts

A left join keeps parents without children by supplying NULL child columns:

SELECT d.department_id,
       COUNT(e.employee_id) AS employee_count
FROM department AS d
LEFT JOIN employee AS e
  ON e.department_id = d.department_id
GROUP BY d.department_id;

COUNT(*) would count the preserved department row even when no employee matched, producing 1. COUNT(e.employee_id) counts only non-NULL matched employee identifiers and correctly produces 0.

19. GROUP BY with Time

Reports often group by temporal periods:

SELECT YEAR(ordered_at) AS order_year,
       MONTH(ordered_at) AS order_month,
       SUM(total_amount)
FROM order_header
GROUP BY YEAR(ordered_at), MONTH(ordered_at);

Grouping only by month number combines January across all years. Include every period component needed for identity.

For filtering, use a range on the original datetime column so an index can be used. Functions in GROUP BY may still require computation or a suitable generated/functional index.

20. ROLLUP

MySQL supports WITH ROLLUP to add hierarchical subtotal and grand-total rows:

SELECT region, category, SUM(amount)
FROM sale
GROUP BY region, category WITH ROLLUP;

Rollup-generated NULL-like grouping output must be distinguished from actual NULL grouping values using the product's grouping facilities where available. Applications should label subtotal levels explicitly rather than assuming every NULL means “total.”

21. Ordering Grouped Results

ORDER BY can use aggregates or their aliases:

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

This requests highest average first and uses department_id as a deterministic tie-breaker.

GROUP BY does not promise the same order even when a current execution happens to emit groups that way.

22. Aggregate Types and Precision

Aggregate result types depend on input type and DBMS rules. SUM over integers may widen, while AVG can return a decimal or approximate type depending on its argument.

Applications should inspect actual result metadata and choose compatible host-language types. Avoid converting a precise database decimal to binary floating point when exact financial behavior is required.

Large counts may exceed a small application integer even when individual table keys do not.

23. Checking a Summary Query

Validate a report systematically:

state what one source row represents;
state the population selected by WHERE;
inspect join cardinalities and sample joined rows;
state what one output group represents;
verify NULL treatment for every aggregate;
test empty groups, unmatched parents and duplicate values;
compare totals against a simpler independent query.

Do not add DISTINCT until the reason for duplicates is understood.

24. Building Trustworthy Aggregations

Choose COUNT(*) for rows and COUNT(column) for known values. Use stable identifiers as grouping keys. Put detail predicates in WHERE and group predicates in HAVING. Make NULL policy explicit with COALESCE only where the domain supports a replacement.

Most serious aggregation errors occur before the aggregate function: an unintended join multiplies rows, a predicate selects the wrong population or grouping merges distinct entities. Reason from row grain through each logical stage and the final summary becomes explainable and testable.