WHERE vs HAVING in MySQL

We can use a conditional clause called the WHERE Clause to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table. The HAVING clause specify filter conditions for a group of rows or aggregates

WHERE clause cannot be used with aggregate function while HAVING can be used with aggregate function. The following is an example −

Let us now create a table.

mysql> create table WhereDemo
   -> (
   -> Price int
   -> );
Query OK, 0 rows affected (0.64 sec)

Inserting records into table.

mysql> insert into WhereDemo values(100);
Query OK, 1 row affected (0.13 sec)

mysql> insert into WhereDemo values(200);
Query OK, 1 row affected (0.21 sec)

mysql> insert into WhereDemo values(300);
Query OK, 1 row affected (0.15 sec)

mysql> insert into WhereDemo values(400);
Query OK, 1 row affected (0.12 sec)

To display all records.

mysql> select *from WhereDemo;

The following is the output.

+-------+
| Price |
+-------+
|   100 |
|   200 |
|   300 |
|   400 |
+-------+
4 rows in set (0.00 sec)

The HAVING clause can be used with aggregate function.

mysql>  select sum(Price) from WhereDemo HAVING Sum(Price) > 500;

The following is the output.

+------------+
| sum(Price) |
+------------+
|       1000 |
+------------+
1 row in set (0.00 sec)

Here, if we will try to use WHERE with aggregate function, an ERROR will get generated.

mysql> select sum(Price) from WhereDemo where Sum(Price) > 200;
ERROR 1111 (HY000): Invalid use of group function