GROUP BY

Lesson 8 of 21 · Part 2 · Summarizing · about 15 minutes

The aggregate functions of the last lesson turn a whole table into one row. But often the question is "how many for each…": how many products are in each category? You could count one category at a time with WHERE, but it's tedious:

1SELECT COUNT(*) FROM products WHERE category = 'Kitchen';2SELECT COUNT(*) FROM products WHERE category = 'Stationery';

4 categories take 4 queries, and with more categories you'd never finish. GROUP BY does it in one.

GROUP BY

GROUP BY column puts the rows that have the same value in that column into one group, and the aggregate functions are worked out once in each group:

1SELECT category, COUNT(*)2FROM products3GROUP BY category4ORDER BY category;

Press Step and watch: first the 10 products, then GROUP BY gathers them into 4 piles by category, each pile becomes one row of the result, and COUNT(*) counts the rows in that pile.

Don't leave out ORDER BY: after grouping, MySQL doesn't promise any particular order.

One row per group

One SELECT can hold several aggregate functions, each worked out once per group:

1SELECT category,2    COUNT(*) AS product_count,3    MIN(price) AS cheapest,4    MAX(price) AS dearest,5    SUM(stock) AS total_stock6FROM products7GROUP BY category8ORDER BY category;

The grouping column (category) has just one value in each group, so it can go in SELECT; each aggregate also gives just one value per group. Every row of the result is one group.

Ranking the groups

How many orders has each customer placed? Group by customer, then sort by the count, most first, and by customer number when the counts are equal:

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3GROUP BY customer_id4ORDER BY order_count DESC, customer_id;

Customer 1 placed 3 orders, the most. ORDER BY can use an alias given in SELECT; lesson 10 explains why.

Customers 6, 9 and 10, who never ordered, aren't in the result: the orders table has no rows for them, so there is no group for them either.

The total of each order

In order_items, one order has several rows. Group by order_id and add up "quantity × unit price" in each group, and you get the amount of each order:

1SELECT order_id,2    SUM(quantity) AS items,3    SUM(quantity * unit_price) AS total4FROM order_items5GROUP BY order_id6ORDER BY order_id;

18 rows of order lines become 12 rows, one per order.

1SELECT status, COUNT(*)2FROM orders3GROUP BY status;

Grouping by several columns

GROUP BY can take several columns; rows form one group only when all of them are equal. Each customer's orders, counted separately by status:

1SELECT customer_id, status, COUNT(*)2FROM orders3GROUP BY customer_id, status4ORDER BY customer_id, status;

Customer 1 has 2 shipped orders and 1 pending, so they make two rows.

NULL forms a group of its own, too. Group the employees by their manager to count how many people each one manages:

1SELECT manager_id, COUNT(*)2FROM employees3GROUP BY manager_id4ORDER BY manager_id;

The group whose manager_id is NULL is the manager herself, who has no manager.

What SELECT can hold

Once you group, SELECT can hold only two kinds of things: the grouping columns and aggregate functions. Any other column gives an error:

1SELECT category, name, COUNT(*)2FROM products3GROUP BY category;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'town.products.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ERROR 1055: name isn't in the GROUP BY. The Electronics group has 4 products and 4 names, yet only one row in the result: which name should it show? MySQL won't pick one for you.

The fix depends on what you want: for the numbers per group, drop name; for one row per product, don't group; for "one" name per group, pick it with an aggregate, such as MIN(name).

Tip: Some databases (and very old MySQL) don't give this error and just pick some name. The result may look right, but only by luck; don't rely on it.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT city FROM customers;

1SELECT product_id, quantity FROM order_items;

Nobody has bought the desk lamp (product 6), so it isn't in the result.

1SELECT title, name, COUNT(*), MAX(salary)2FROM employees3GROUP BY title4ORDER BY MAX(salary) DESC;

What you learned

• GROUP BY column puts rows with the same value in one group; aggregates are worked out per group, one row per group.
• GROUP BY can take several columns, and rows group together only when all are equal; NULL forms its own group.
• Groups come in no particular order; fix it with ORDER BY.
• SELECT can hold only the grouping columns and aggregates; another column gives ERROR 1055.