MySQL HAVING
In MySQL, the HAVING condition is used with the GROUP BY clause to group the fetched data by one or more columns only when the condition is TRUE.
Syntax: To group the rows by values in multiple columns.
SELECT expressions FROM table_name GROUP BY columns HAVING having_conditions;
Parameters:
having_conditions: It is used to specify the conditions that apply only to the result obtained after the successful execution of the GROUP BY command, to restrict the groups of returned rows.
Example 1: Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 50 |
| 4 | Grocery | 30 |
| 5 | Toys | 50 |
Query:
SELECT quantity FROM items GROUP BY quantity HAVING quantity > 30;
Output:
QUANTITY 45 50
Explanation:
The ‘items’ is an already existing table from which we are retrieving the unique values of the ‘quantity’ and grouping them for the value of ‘quantity’ greater than 30.
Example 2: Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 50 |
| 4 | Grocery | 30 |
| 5 | Toys | 50 |
Query:
SELECT quantity FROM items WHERE id > 2 GROUP BY quantity HAVING quantity > 30;
Output:
QUANTITY 50
Explanation:
The ‘items’ is an already existing table from which we are retrieving the unique values of the ‘quantity’ for the value of the ‘id’ greater than 2 and grouping them for the value of ‘quantity’ greater than 30.
Example 3: MySQL GROUP BY with COUNT function. Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 50 |
| 4 | Grocery | 30 |
| 5 | Toys | 50 |
Query:
SELECT quantity, COUNT(*) AS “Number of Items” FROM items GROUP BY quantity HAVING COUNT(*) > 1;
Output:
| QUANTITY | Number of Items |
| 30 | 2 |
| 50 | 2 |
Explanation:
The ‘items’ is an already existing table from which we are counting the number of items and grouping them by the same quantity value when the count is greater than 1.
Example 4: MySQL GROUP BY with SUM function. Items table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 100 |
| 4 | Electronics | 90 |
| 5 | Sports | 50 |
Query:
SELECT name, SUM(quantity) AS “Quantity” FROM items GROUP BY name HAVING SUM(quantity) >= 100;
Output:
| NAME | QUANTITY |
| Electronics | 120 |
| Fashion | 100 |
Explanation:
The ‘items’ is an already existing table from which we are calculating the sum of quantities and grouping them by the same item names when the sum is greater than or equal to 100.