Master MySQL GROUP BY: From Basics to Advanced Aggregations

Overview

Often you need to know the average spending of customers who bought cosmetics on a specific day or the purchase proportion across age groups; grouping queries let you split data into logical groups and compute aggregates for each group.

GROUP BY Syntax

<code>SELECT cname, group_fun, ... FROM tname [WHERE condition] GROUP BY group_expression [HAVING group_condition];</code>

group_fun denotes an aggregate function, group_expression is the grouping expression (multiple expressions separated by commas), and group_condition filters groups after aggregation.

Aggregate Functions

AVG() – returns the average value of a numeric column (ignores NULL).

COUNT() – counts rows; COUNT(*) counts all rows, while COUNT(col) ignores NULLs.

MAX() – returns the maximum value (ignores NULL).

MIN() – returns the minimum value (ignores NULL).

SUM() – returns the sum of a numeric column (ignores NULL).

AVG() Example

<code>SELECT AVG(age) FROM user2;</code>

Result: avg(age) = 23.8571 (NULL rows are excluded).

COUNT() Example

<code>SELECT COUNT(*) FROM user2 WHERE sex=0;</code>

Result: 5 rows (COUNT(*) counts NULLs, COUNT(col) would ignore them).

MAX() and MIN() Example

<code>SELECT MAX(age), MIN(age) FROM user2;</code>

Result: max(age)=33, min(age)=20.

SUM() Example

<code>SELECT SUM(age) FROM user2;</code>

Result: sum(age)=167.

Grouping Queries

Assume a table t_order with columns orderid, uid, uname, amount, time, year .

Single‑Field Grouping

<code>SELECT uid, COUNT(uid), SUM(amount) FROM t_order GROUP BY uid;</code>

Shows each user’s order count and total amount.

Multi‑Field Grouping

<code>SELECT uid, COUNT(uid) AS nums, SUM(amount) AS totalamount, year FROM t_order GROUP BY uid, year;</code>

Aggregates per user per year.

Filtering Before Grouping (WHERE)

<code>SELECT uid, COUNT(uid) AS nums, SUM(amount) AS totalamount, year FROM t_order WHERE time > '2019-08-01' GROUP BY uid, year;</code>

Filtering After Grouping (HAVING)

<code>SELECT uid, COUNT(uid) AS nums, SUM(amount) AS totalamount, year FROM t_order WHERE time > '2019-08-01' GROUP BY uid, year HAVING nums > 1;</code>

WHERE filters rows before aggregation; HAVING filters groups after aggregation.

Ordering and Limiting

<code>SELECT uid, COUNT(uid) AS nums, SUM(amount) AS totalamount FROM t_order GROUP BY uid ORDER BY totalamount DESC LIMIT 1;</code>

Returns the user with the highest total spending.

Keyword Execution Order

The correct order when combining clauses is: WHERE → GROUP BY → HAVING → ORDER BY → LIMIT . Changing this order causes syntax errors.

Summary

1) In GROUP BY statements, fields after SELECT must be either grouping fields or aggregate columns; otherwise an error occurs. 2) The execution sequence of grouping keywords is WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, and it must not be altered.