WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL HAVING Clause


HAVING clause

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

The

HAVING clause allows us to filter the grouped data.

SQL HAVING syntax

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 welookups sample database.

Here is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/| 1     | USA     |
| 2  | Indiatimes          | https://www.Indiatimes.com/| 13    | IN      |
| 3  | zomato      | http://www.zomato.com/| 4689  | IN      |
| 4  | indiamart          | http://indiamart.com/   | 20    | IN      |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Here is the data for the "access_log" site visit log:

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 sites with more than 200 total visits.

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;

The result of executing the above SQL is as follows:

Now we want to find sites with more than 200 total visits and alexa rank less than 200.

We add a normal 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;

The result of executing the above SQL is as follows: