MySQL
In this tutorial we will learn about the HAVING clause in MySQL.
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.
SELECT column_name FROM table_name WHERE condition GROUP BY column_name HAVING condition;
For this tutorial we will be using the employee, orders and comments table given below.
employee
orders
comments
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)
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.
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.
e
c
And we are joining the two tables using the column employeeid which is common in both the tables.
employeeid
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.