Easy Tutorial
Sql Having Sql Func Mid Sql Between Sql Drop Sql Wildcards Sql Join Inner Sql Join Full Sql Func Lcase Sql Where Sql Join Sql Func Round Sql Primarykey Sql Func First Sql Func Len Sql Distinct Sql Func Ucase Sql Function Sql Delete Sql Autoincrement Func Curtime Sql Update Sql Isnull Func Date Sub Sql Constraints Sql Intro Sql Dates Sql Select Into Sql Hosting Sql Create Table Sql Func Format Sql View Sql Func Count Sql Index Sql Like Sql Datatypes General Sql Datatypes Func Convert Sql Func Avg Sql Foreignkey Sql Func Sum Sql Join Right Func Curdate Sql Alias Sql Union Func Date Add Sql Groupby Sql Summary Sql In Sql Null Values Sql Syntax Sql Tutorial Sql Insert Into Select Sql And Or Sql Join Left Sql Func Now Func Date Sql Top Index Sql Create Db Sql Unique Sql Check Func Extract Sql Alter Sql Notnull Func Now Sql Select Sql Insert Sql Func Max Sql Exists Sql Func Min Func Date Format Func Datediff Mysql Func Getdate Sql Orderby Func Datediff Sql Func Last Sql Create Index Func Dateadd Sql Quickref Func Datepart
❮ Home Sql Func Mid ❯

SQL HAVING Clause

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 data after the grouping has been performed.

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

Below is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | Taobao       | https://www.taobao.com/   | 13    | CN      |
| 3  | tutorialpro.org      | http://www.tutorialpro.org/    | 4689  | CN      |
| 4  | Weibo        | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below is the data from the "access_log" table, which records website visits:

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 total 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 gives the following output:

Now we want to find websites with total visits greater than 200 and an Alexa rank less than 200.

We add a regular WHERE clause to the 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)
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

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 above SQL query outputs the following results:

❮ Home Sql Func Mid ❯