MySQL - GROUP BY

In this tutorial we will learn about GROUP BY in MySQL.

GROUP BY statement

As the name suggests we use the GROUP BY statement to group results based on columns. And we use this with aggregate functions like SUM, COUNT etc.

GROUP BY syntax

SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name;

Order table

For this tutorial we will be using the following orders table.

mysql> SELECT * FROM orders;
+---------+------------+--------+-------------+---------------------+---------------------+
| orderid | employeeid | amount | orderstatus | lastmodified        | created             |
+---------+------------+--------+-------------+---------------------+---------------------+
|       1 | e03        |  15.00 | OPEN        | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
|       2 | e01        |  25.50 | OPEN        | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
|       3 | e05        | 100.70 | CLOSED      | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
|       4 | e02        |  22.18 | OPEN        | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
|       5 | e04        |   9.50 | CANCELLED   | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
|       6 | e04        |  99.99 | OPEN        | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
+---------+------------+--------+-------------+---------------------+---------------------+
6 rows in set (0.00 sec)

Example #1

In the following example we will find the total number of orders placed by each employee.

To solve this problem we will group the result by employeeid. And we will also use the COUNT function to find the total number of orders.

mysql> SELECT employeeid, COUNT(orderid) AS total_order
FROM orders 
GROUP BY employeeid;

+------------+-------------+
| employeeid | total_order |
+------------+-------------+
| e01        |           1 |
| e02        |           1 |
| e03        |           1 |
| e04        |           2 |
| e05        |           1 |
+------------+-------------+
5 rows in set (0.00 sec)

So, in the above output we can see that employeeid 'e04' has placed 2 orders while all other employees have placed single order.

Example #2

In the following example we will display total number of OPEN orders for each employees.

To solve this we will use the WHERE clause to check the orderstatus. And we will group the result by employeeid. And to find the total number of orders we will use the COUNT function.

mysql> SELECT employeeid, COUNT(orderid) AS total_open_order
FROM orders
WHERE orderstatus = 'OPEN'
GROUP BY employeeid;

+------------+------------------+
| employeeid | total_open_order |
+------------+------------------+
| e01        |                1 |
| e02        |                1 |
| e03        |                1 |
| e04        |                1 |
+------------+------------------+
4 rows in set (0.00 sec)

For the above output we can tell that each employee has 1 OPEN order.

Example #3

In the following example we will display the total number of order based on the order status.

To solve this we will group the result by orderstatus column.

mysql> SELECT orderstatus, COUNT(orderid) AS total_order
FROM orders
GROUP BY orderstatus;

+-------------+-------------+
| orderstatus | total_order |
+-------------+-------------+
| OPEN        |           4 |
| CLOSED      |           1 |
| CANCELLED   |           1 |
+-------------+-------------+
3 rows in set (0.00 sec)

From the given output we can tell that there are 4 OPEN orders, 1 CLOSED and 1 CANCELLED order.