What is the usage of having in mysql

In mysql, the having clause is used to filter various data after grouping. It is usually used in conjunction with "group by". This statement makes up for the shortcoming that the where keyword cannot be used in conjunction with aggregate functions.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the usage of having in mysql

The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with aggregate functions.

HAVING clause allows us to filter each group of data after grouping.

SQL HAVING syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Demo database

In this tutorial, we will use the RUNOOB sample database.

The following is the data selected from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

The following is the data from the "access_log" website access record table:

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SQL HAVING instance

Now we want to find websites with total visits greater than 200.

We use the following SQL statement:

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

The output result of executing the above SQL is as follows:

Recommended learning: mysql video Tutorial

The above is the detailed content of What is the usage of having in mysql. For more information, please follow other related articles on the PHP Chinese website!