MySQL - HAVING

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.

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.