SQL Group By
GROUP BY gathers rows that share a value into one group. The query then returns one result row for each group.
Products per category
GROUP BY category forms one group per category. COUNT(*) then counts the rows inside each group.
SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category;Stock per category
SUM here adds stock inside each group, not across the whole table. Each category gets its own total.
SELECT category, SUM(stock) AS stock_total
FROM products
GROUP BY category
ORDER BY category;Customers per country
The customers table groups by country in the same way. The USA group holds two rows, and every other group holds one.
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY country;Example
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;Key points
- GROUP BY: the clause that gathers rows into groups by a shared value
- The result holds one row per group.
- An aggregate function in the SELECT list sums up each group on its own.
- The SELECT list names the grouping column beside the aggregate.
- ORDER BY can sort the group rows like any other result.
In this example, GROUP BY customer_id gathers the ten orders into seven groups. COUNT(*) counts the orders inside each group. The result holds one row per customer who ordered. Customer 8 placed no orders, so no group appears for that id.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
How many rows does a GROUP BY query return?