GROUP BY
In this page:
Simple Grouping
The GROUP BY clause collects matching data rows together into summary groups based on shared column values. It is used with aggregate functions to summarize your data, condensing many rows into one row per group.
Example: Simple Grouping
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT customer_id FROM orders GROUP BY customer_id;Grouping with COUNT()
You can count how many rows belong to each group by pairing GROUP BY with COUNT(). This is the most common way to generate item statistics, like how many orders each customer has placed.
Example: Grouping with COUNT()
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;Grouping with SUM()
You can calculate mathematical totals for each group by pairing GROUP BY with SUM(). This helps you calculate product sales or inventory totals broken down by category instead of lumped into one number.
Example: Grouping with SUM()
CREATE TABLE sales (id INT, category TEXT, amount INT);
INSERT INTO sales VALUES (1, 'Books', 50), (2, 'Toys', 30), (3, 'Books', 20);
SELECT category, SUM(amount) AS total_sales FROM sales GROUP BY category;Grouping on Multiple Columns
You can group rows by more than one column at once, such as grouping by both region and product category. This creates highly detailed sub-groups in your query results, one row per unique combination of the grouped columns.
Example: Grouping on Multiple Columns
CREATE TABLE sales (id INT, region TEXT, category TEXT, amount INT);
INSERT INTO sales VALUES (1, 'North', 'Books', 50), (2, 'North', 'Toys', 20), (3, 'South', 'Books', 30);
SELECT region, category, SUM(amount) AS total FROM sales GROUP BY region, category;GROUP BY with ORDER BY
You can sort your grouped summary data using ORDER BY after the GROUP BY has collapsed the rows. This lets you rank your groups from highest to lowest metrics, like finding your top-selling categories first.
Example: GROUP BY with ORDER BY
CREATE TABLE sales (id INT, category TEXT, amount INT);
INSERT INTO sales VALUES (1, 'Books', 50), (2, 'Toys', 90), (3, 'Books', 20);
SELECT category, SUM(amount) AS total FROM sales GROUP BY category ORDER BY total DESC;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: