MySQL HAVING Clause

The MySQL HAVING clause is used to filter the results of an SQL query that includes a GROUP BY clause. 

It allows you to specify a condition that must be met by the groups that are created by the GROUP BY clause.

The HAVING clause is similar to the WHERE clause, but while the WHERE clause is used to filter individual rows, the HAVING clause filters groups of rows. 

The HAVING clause is always used after the GROUP BY clause in a SELECT statement.

The GROUP BY clause in MySQL is used to group together rows in a table based on one or more columns - it is typically used in combination with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(), which are used to perform calculations on the grouped data. 

What we have in a table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

Simple "HAWING" and  "WERE" combination: 

mysql> SELECT * FROM Stores
    -> WHERE price > 50
    -> HAVING available > 50;
+------+--------+------------+----------+-----------+-------+
| s_id | city   | store_name | product  | available | price |
+------+--------+------------+----------+-----------+-------+
|    1 | London | London_1   | Gold PSU |       153 |   100 |
|    2 | London | London_2   | Gold PSU |        75 |   100 |
+------+--------+------------+----------+-----------+-------+
2 rows in set (0.02 sec)

mysql>

Our query selects all columns from the "Stores" table where the "price" column is greater than 50. 

Then, the HAVING clause is used to further filter the results to only include records where the "available" column is greater than 50.

So, WHERE clause is used to filter individual rows based on a specific condition, and the HAVING clause is used to filter groups of rows based on a specific condition.

mysql> SELECT * FROM Stores
    -> WHERE store_name LIKE "%on%"
    -> HAVING price > 25;
+------+--------+------------+----------+-----------+-------+
| s_id | city   | store_name | product  | available | price |
+------+--------+------------+----------+-----------+-------+
|    1 | London | London_1   | Gold PSU |       153 |   100 |
|    2 | London | London_2   | Gold PSU |        75 |   100 |
+------+--------+------------+----------+-----------+-------+
2 rows in set (0.00 sec)

mysql>

The query selects all columns from the "Stores" table where the "store_name" column contains the string "on". 

Then, the HAVING clause is used to further filter the results to only include records where the "price" column is greater than 25.