MySQL
In this tutorial we will learn about GROUP BY in MySQL.
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.
SELECT column_name FROM table_name WHERE condition GROUP BY column_name;
For this tutorial we will be using the following orders table.
orders
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)
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.
employeeid
COUNT
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.
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.
orderstatus
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.
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.