Summary: In this tutorial, you will learn how to use MySQL GROUP BY to group selected records into a set of summary records by the one or more column’s value or expression.
Introducing to MySQL GROUP BY clause
The MySQL GROUP BY clause is used with SQL SELECT statement to to group selected records into a set of summary records by the one or more column’s value or expression. The MySQL GROUP BY clause is an optional part of the SQL SELECT statement. The MySQL GROUP BY clause must appear after the WHERE clause or FROM clause if WHERE clause is omitted of the SQL SELECT statement. The MySQL GROUP BY clause consists of GROUP BY keyword followed by a list of expressions separated by commas. The following illustrates the MySQL GROUP BY clause:
1 |
SELECT col1_,col_2,... col_n, aggregate_function(expression) |
4 |
GROUP BY col_1, col_2, ... col_n |
We will examine the GROUP BY clause in details. Let’s take a look at the order table in our sample database.
Example of MySQL GROUP BY clause
Now, suppose you want to get groups for each order, you can use the MySQL GROUP BY clause as follows:
+------------+
| status |
+------------+
| Cancelled |
| Disputed |
| In Process |
| On Hold |
| Resolved |
| Shipped |
+------------+
6 rows in set (0.00 sec)
It seems that the GROUP BY clause only scans for unique occurrences in the status column and return the result set. However if you look at the data of the orders table you will see that each row in result set above is summary of records that represent a group orders that have the same status on the status column.
MySQL GROUP BY with aggregate function
The aggregate functions allow you to perform calculation of a set of records and return a single value. The most common aggregate functions are SUM, AVG, MAX, MIN and COUNT. For more information on aggregate functions, please refer to the aggregate functions in MySQL tutorial.
Aggregate functions are used with MySQL GROUP BY clause to perform calculation on each group of records on return a single value for each row. Let’s say if you want to know how many orders in each status group you can use the COUNT function as follows:
1 |
SELECT status, count(*) |
+------------+----------+
| status | count(*) |
+------------+----------+
| Cancelled | 6 |
| Disputed | 3 |
| In Process | 6 |
| On Hold | 4 |
| Resolved | 4 |
| Shipped | 303 |
+------------+----------+
6 rows in set (0.00 sec)
The query counts number of orders for each order’s status.
MySQL GROUP BY vs. ANSI SQL GROUP BY
MySQL follows ANSI SQL. However, there are two differences the way GROUP BY works in MySQL and ANSI SQL.
- In ANSI SQL you must group by all columns you specifies in the SELECT clause. MySQL does not have this restriction. You can have additional columns in the SELECT clause that are not in the GROUP BY clause.
- 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 result of the query above in the descending order, you can do it as follows:
1 |
SELECT status, count(*) |
+------------+----------+
| status | count(*) |
+------------+----------+
| Shipped | 303 |
| Resolved | 4 |
| On Hold | 4 |
| In Process | 6 |
| Disputed | 3 |
| Cancelled | 6 |
+------------+----------+
6 rows in set (0.00 sec)
As you see the status of orders now are in reverse alphabetical order. By default it is ascending order determined by ASC.
In this tutorial, you have learned how to use MySQL GROUP BY to retrieve data records in-group. You’ve also learned how to sort the result set in the MySQL GROUP BY clause supported only in MySQL.
Related posts:
- Using MySQL SELECT Statement to Query Data
- MySQL LEFT JOIN
- Combining Result Sets with MySQL UNION
- Selecting Data with SQL IN
- MySQL INNER JOIN
