SQL HAVING
SQL HAVINGclause
HAVING clause
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
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 PHP sample database.
The following is the data selected from the "Websites" table:
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | http://www.php.cn/ | 4689 | CN |
| 4 | Weibo | 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 of the "access_log" website access record table:
+-----+--------- +-------+------------+
| 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 | 201 6-05 -16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+----------+------+--- ---------+
9 rows in set (0.00 sec)
SQL HAVING instance
Now we want to find the total number of visits More than 200 websites.
We use the following SQL statement:
Example
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
Execute the above SQL and the output result is as follows:
Now we want to find the total number of visits greater than 200 website, and the alexa ranking is less than 200.
We add a common WHERE clause to the SQL statement:
Example
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
The output result of executing the above SQL is as follows:
