Having clause in MySQL

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.