Master SQL GROUP BY: From Simple Aggregations to Rollup and Joins

The GROUP BY clause groups rows that share the same values in specified columns, optionally filtered by HAVING and extended with WITH ROLLUP to add a summary row.

1. Basic GROUP BY

When used alone, the query returns one row per distinct group. select * from employee group by sex; This returns two rows, one for each gender.

2. GROUP BY with GROUP_CONCAT()

Combines values from each group into a comma‑separated list.

select sex, group_concat(name) from employee group by sex;

Result example:

sex | group_concat(name)
女  | 小红, 小兰
男  | 张三, 王五, 王六

3. GROUP BY with aggregate functions

Common aggregates include COUNT(), SUM(), AVG(), MAX(), and MIN().

select sex, count(sex) from employee group by sex;

Result:

sex | count
女  | 1
男  | 3

4. Using HAVING with GROUP BY

HAVING

filters groups after aggregation.

select sex, count(sex) from employee group by sex having count(sex) >= 3;

Result shows only groups meeting the condition (e.g., male with count 3).

5. Grouping by multiple columns

select * from employee group by d_id, sex;

The result is first grouped by d_id and then by sex within each department.

6. GROUP BY with WITH ROLLUP

WITH ROLLUP

adds a final row containing totals for all groups.

select sex, count(sex) from employee group by sex with rollup;

Result example:

sex | count
女  | 1
男  | 5
NULL| 6

7. Join queries

7.1 Inner join

select num, name, employee.d_id, age, d_name
from employee, department
where employee.d_id = department.d_id;

Matches rows where the department IDs are equal.

7.2 Left join

select num, name, employee.d_id, age, d_name
from employee left join department on employee.d_id = department.d_id;

Returns all rows from employee and matching rows from department; non‑matching department fields are NULL.

7.3 Right join

select num, name, employee.d_id, age, d_name
from employee right join department on employee.d_id = department.d_id;

Returns all rows from department and matching rows from employee.

8. Common aggregate functions

COUNT(*)

– counts rows; often used with GROUP BY. SUM(column) – sums numeric values; ignores non‑numeric fields. AVG(column) – calculates the average. MAX(column) and MIN(column) – return the highest and lowest values; for strings they compare ASCII codes.

These functions enable powerful data summarization when combined with GROUP BY and HAVING.