MySQL - HAVING
MySQL
In this tutorial we will learn about the HAVING clause in MySQL.
The HAVING clause
We use the HAVING clause with the GROUP BY statement to filter the result set. Since we can not use the aggregate function like COUNT in the GROUP BY so we have to use the HAVING clause for filtering.
HAVING syntax
SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition;
Tables
For this tutorial we will be using the employee, orders and comments table given below.
Table: employee
mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01 | Yusuf | Shakeel | yusuf@example.com | 7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03 | Jane | Doe | janedoe@example.com | 9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04 | Tin | Tin | tintin@example.com | 6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05 | Bob | Coder | bobcoder@example.com | 7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)
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)
Table: comments
mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody | lastmodified | created |
+-----------+------------+---------------------+---------------------+---------------------+
| 1 | e03 | Awesome | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
| 2 | e03 | Hello World | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
| 3 | e01 | Happy | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
| 4 | e04 | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
| 5 | e01 | Keep coding.... | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)
Example #1
In the following example we will list the total number of orders placed by each employee with the condition that there are at least 2 orders placed by the employee.
So, first we will list total number of orders placed by the employees only using the GROUP BY.
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)
Now we will fetch total orders placed by employess who placed at least 2 orders.
mysql> SELECT
employeeid, COUNT(orderid) AS total_order
FROM
orders
GROUP BY employeeid
HAVING COUNT(orderid) >= 2;
+------------+-------------+
| employeeid | total_order |
+------------+-------------+
| e04 | 2 |
+------------+-------------+
1 row in set (0.00 sec)
We can see that only one employee e04 has placed at least 2 orders.
Example #2
In this example we will display the employeeid, firstname, lastname and total comments posted by the employees provided they have at least 2 comments.
So, first we will list total comments posted by the employees. For this we will group the result using GROUP BY employeeid.
Since the employee detail is in the employee table and comments detail is in the comments table so, we will join the two tables.
mysql> SELECT
e.employeeid,
e.firstname,
e.lastname,
COUNT(c.commentid) AS total_comment
FROM
employee e,
comments c
WHERE
e.employeeid = c.employeeid
GROUP BY e.employeeid;
+------------+-----------+----------+---------------+
| employeeid | firstname | lastname | total_comment |
+------------+-----------+----------+---------------+
| e01 | Yusuf | Shakeel | 2 |
| e03 | Jane | Doe | 2 |
| e04 | Tin | Tin | 1 |
+------------+-----------+----------+---------------+
3 rows in set (0.00 sec)
From the above output we can see that employee e01 and e03 posted 2 comments while employee e04 posted only once.
Note! In the above example we are using alias e for the employee table and c for the comments table.
And we are joining the two tables using the column employeeid which is common in both the tables.
Now, we will list only those employee who commented at least 2 times.
For this we will first group the result by employeeid and then apply the having clause to the number of comments.
SELECT
e.employeeid,
e.firstname,
e.lastname,
COUNT(c.commentid) AS total_comment
FROM
employee e,
comments c
WHERE
e.employeeid = c.employeeid
GROUP BY e.employeeid
HAVING COUNT(c.commentid) >= 2;
+------------+-----------+----------+---------------+
| employeeid | firstname | lastname | total_comment |
+------------+-----------+----------+---------------+
| e01 | Yusuf | Shakeel | 2 |
| e03 | Jane | Doe | 2 |
+------------+-----------+----------+---------------+
2 rows in set (0.00 sec)
And we get 2 employees who posted comments at least 2 times.
ADVERTISEMENT