Summary: in this tutorial, you will learn how to use MySQL GROUP BY to group rows into subgroups based on columns or values returned by an expression.
Introducing to MySQL GROUP BY clause
The MySQL GROUP BY clause is used with the SELECT statement to group rows into subgroups by the one or more values of columns or expressions.
The MySQL GROUP BY clause is an optional part of the
SELECT statement. It must appear after the
WHERE clause. The MySQL GROUP BY clause consists of the
GROUP BY keyword followed by a list of comma-separated columns or expressions.
The following illustrates the MySQL GROUP BY clause syntax:
SELECT c1,c2,... cn, aggregate_function(expression) FROM table WHERE where_conditions GROUP BY c1, c2, ... cn
MySQL GROUP BY Examples
Let’s take a look at the
orders table in the sample database. Suppose you want to group values of the order status into subgroups, you use the
GROUP BY clause with the
status column as the following query:
SELECT status FROM orders GROUP BY status
You can see that the
GROUP BY clause returns unique occurrences of
status values. It works like the DISTINCT operator as using in the following query:
SELECT DISTINCT status FROM orders
MySQL GROUP BY with aggregate functions
The aggregate functions allow you to perform calculation of a set of records and return a single value. The most common aggregate functions are
An aggregate functions is often used with the MySQL GROUP BY clause to perform calculation on each subgroup and return a single value for each subgroup. For example, if you want to know how many orders in each status, you can use the
COUNT function with the
GROUP BY clause as follows:
SELECT status, count(*) FROM orders GROUP BY status
MySQL GROUP BY vs. ANSI SQL GROUP BY
MySQL follows ANSI SQL. However, MySQL gives you more flexibility when using the GROUP BY clause:
- In ANSI SQL, you must list all columns that you use in the
SELECTclause in the
GROUP BYclause. MySQL does not have this restriction. MySQL allows you to have additional columns in the
SELECTclause that are not specified in the
- MySQL also allows you to sort the group order in which the results are returned. The default order is ascending.
If you want to see the
status and the number of orders in descending order, you can use the
GROUP BY clause with
DESC as the following query:
SELECT status, count(*) FROM orders GROUP BY status DESC;
Notice that we use
DESC in the
GROUP BY clause to sort the
status in descending order. You can specify
ASC explicitly in the
GROUP BY clause to sort the groups in ascending order.
In this tutorial, we have shown you how to use the MySQL GROUP BY clause to group rows into subgroups based on columns or values returned from an expression.