MySQL GROUP BY

The GROUP BY clause organises rows that share the same value in one or more columns into groups, so that aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() can be applied to each group independently. Instead of one result for the whole table, you get one result per group.

For example, without GROUP BY, SUM(price) gives you the total of all products. With GROUP BY category, you get the total per category.

---

Syntax

SELECT column, aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column
ORDER BY column;

---

Sample Table

id	name	category	price	quantity_sold
1	Laptop	Electronics	55000.00	5
2	Phone	Electronics	25000.00	12
3	T-Shirt	Clothing	800.00	40
4	Headphones	Electronics	3500.00	18
5	Jeans	Clothing	2200.00	22
6	Tablet	Electronics	35000.00	8
7	Jacket	Clothing	4500.00	15

---

GROUP BY with COUNT()

Count how many products belong to each category:

SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

category	total_products
Electronics	4
Clothing	3

---

GROUP BY with SUM()

Calculate total units sold per category:

SELECT category, SUM(quantity_sold) AS total_sold
FROM products
GROUP BY category;

category	total_sold
Electronics	43
Clothing	77

---

GROUP BY with Multiple Aggregate Functions

You can use several aggregate functions in the same GROUP BY query:

SELECT
    category,
    COUNT(*)                          AS total_products,
    SUM(quantity_sold)                AS total_sold,
    ROUND(AVG(price), 2)              AS avg_price,
    MIN(price)                        AS cheapest,
    MAX(price)                        AS most_expensive
FROM products
GROUP BY category;

category	total_products	total_sold	avg_price	cheapest	most_expensive
Electronics	4	43	29625.00	3500.00	55000.00
Clothing	3	77	2500.00	800.00	4500.00

---

GROUP BY with WHERE

WHERE filters rows before grouping. Only the rows that pass the WHERE condition are included in each group:

-- Count products per category, but only those priced above 2000
SELECT category, COUNT(*) AS total
FROM products
WHERE price > 2000
GROUP BY category;

---

GROUP BY with ORDER BY

You can sort the grouped results using ORDER BY. Sort by an aggregate result to rank your groups:

-- Categories ranked by total units sold, highest first
SELECT category, SUM(quantity_sold) AS total_sold
FROM products
GROUP BY category
ORDER BY total_sold DESC;

---

GROUP BY Multiple Columns

Group by more than one column to create finer groups. Each unique combination of the grouped columns becomes its own group:

-- Count products per category AND per price tier
SELECT category, price, COUNT(*) AS count
FROM products
GROUP BY category, price
ORDER BY category, price;

---

Query Clause Order

When using GROUP BY, the clause order in a query is:

SELECT ...
FROM ...
WHERE ...       -- filter rows before grouping
GROUP BY ...    -- group the filtered rows
ORDER BY ...    -- sort the grouped results
LIMIT ...       -- cap the output

---

Key Points to Remember

• GROUP BY collapses rows with the same value into a single group per unique value.
• Every non-aggregated column in SELECT must appear in GROUP BY.
• WHERE filters before grouping; use HAVING (next lesson) to filter after grouping.
• You can group by multiple columns — each unique combination forms its own group.
• Sort grouped results with ORDER BY — you can sort by an aggregate result like ORDER BY COUNT(*) DESC.