SQL HAVINGClauses

HAVING Clause

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

The HAVING clause allows us to filter the grouped data after grouping.

SQL HAVING Syntax

SQL HAVING Syntax

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;

Parameter description:

  • column1: The column to be retrieved.
  • aggregate_function(column2): An aggregate function, such as SUM, COUNT, AVG, etc., applied tocolumn2the value of.
  • table_name: The table from which to retrieve data.
  • GROUP BY column1: According tocolumn1Group the data based on the values of the column.
  • HAVING condition: A condition used to filter the grouped results. Only groups that satisfy the condition will be included in the result set.

Demo Database

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

The following is 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  | Example      | http://www.example.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 log 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 Example

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

We use the following SQL statement:

Example

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;

Executing the above SQL produces the following output:

Now we want to find websites with a total number of visits greater than 200 and an Alexa ranking less than 200.

We add an ordinary WHERE clause to the SQL statement:

Example

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites 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;

Executing the above SQL produces the following output:

Other Extensions