MySQL HAVING

HAVING clause is used in SELECT statement to specify filter conditions for a group of rows or aggregates.

HAVING clause is often used with GROUP BY clause to filter groups based on a specified condition. If GROUP BY clause is neglected, HAVING clause behaves like WHERE clause.
 
Here is the syntax of HAVING clause:

SELECT column1, column2, ...
FROM tableName
WHERE condition
GROUP BY columnX
HAVING condition
ORDER BY columnX;

 
MySQL evaluates HAVING clause after SELECT, FROM, WHERE, and GROUP BY clauses and before ORDER BY and LIMIT clauses.
 

 
Let’s take a few examples of using HAVING clause to see how it works. For this we will use the “Stock” table.

> SELECT * FROM Stock

+----------+---------------+-----------+---------------+
|    ID    |    Product    |     qt    |   Category    |
+----------+---------------+-----------+---------------+
|     1    |   Pc          |     35    |   Computer    |
|     2    |   Keyboard    |     10    |   Computer    |
|     3    |   Mouse       |     12    |   Computer    |
|     4    |   Chocolate   |     114   |   Food        |
|     5    |   Mushroom    |     600   |   Food        |
+----------+---------------+-----------+---------------+

 

Example 1- Using SUM function

Let’s look at the following example which uses HAVING clause with SUM function.

You can also use SUM function to return the product name and the total quantity (for this product). HAVING clause will filter the results so only products with a total quantity greater than 5 are returned.

SELECT product, SUM(qte) AS "Total quantity"
FROM stock
GROUP BY product
HAVING SUM(qte) > 5;

Output:

+----------+----------------+
| Product  | Total quantity |
+----------+----------------+
| Pc       |             94 |
| Keyboard |             15 |
| Mouse    |             6  |
+----------+----------------+

 

Example 2 – Using MIN function

Now let’s look at how to use HAVING clause with MIN function in MySQL.

You can also use MIN function to return the name of each product and the minimum quantity in stock. HAVING clause will only return products where the minimum quantity is less than 5.

SELECT product, MIN(qte) AS "Minimum Quantity"
FROM stock
GROUP BY product
HAVING MIN(qte) < 5;

Output:

+----------+------------------+
| Product  | Minimum Quantity |
+----------+------------------+
| RAM      |              1   |
| WebCam   |              2   |
+----------+------------------+

 

Example 3 – Using MAX function

Now let’s look at how to use HAVING clause with MAX function in MySQL.

You can also use MAX function to return the name of each product and the maximum quantity in the stock. HAVING clause will only return products where the maximum quantity is less than 5.

SELECT product, MAX(qte) AS "Maximum Quantity"
FROM stock
GROUP BY product
HAVING MAX(qte) > 5;

Output:

+----------+------------------+
| Product  | Maximum Quantity |
+----------+------------------+
| Keyboard |              10  |
| Speakers |              35  |
+----------+------------------+

 

Example 4 – Using COUNT function

Let’s take a look at how to use HAVING clause with COUNT function in MySQL.

You can use COUNT function to return the name of the product and the number of orders (for that product) that are in the “Computer” category. HAVING clause will filter the results so that only products with more than 5 orders are returned.

SELECT product, COUNT(*) AS "Number of orders"
FROM stock
WHERE categorie = 'Computer'
GROUP BY product
HAVING COUNT(*) > 5;

Output:

+----------+------------------+
| Product  | Number of orders |
+----------+------------------+
| Keyboard |               6  |
+----------+------------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More