MySQL HAVING Clause

In MySQL, the HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a query based on aggregate functions. It provides a way to apply conditions to the grouped results, which cannot be achieved using the WHERE clause alone.

The HAVING clause is essential when you need to filter groups of data that are returned by a GROUP BY query. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause filters groups after the aggregation has been performed.

MySQL HAVING Clause

The HAVING clause allows you to apply conditions to the groups created by the GROUP BY clause. It is particularly useful when you want to filter data based on aggregate values such as sums, averages, counts, etc. Essentially, it works like a WHERE clause but is used with grouped data.

Syntax:

SELECT column1, column2, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column1, column2
HAVING AGGREGATE_FUNCTION(column_name) condition;

Where:

• column1, column2: Columns to include in the result.
• table_name: The table from which to retrieve data.
• AGGREGATE_FUNCTION(column_name): An aggregate function applied to a column.
• condition: The condition to filter the aggregated results.

Demo MySQL Database

To get a good understanding of the HAVING Clause, we'll first create a sample database and table, then run some queries to see how the HAVING Clause works.

Let's create a table named sales with the following sample data:

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    quantity INT,
    price DECIMAL(10, 2)
);

INSERT INTO sales (product, quantity, price) VALUES
('Laptop', 2, 1000),
('Mouse', 10, 20),
('Laptop', 3, 1000),
('Mouse', 6, 20),
('Keyboard', 5, 50),
('Headphones', 5, 800),
('Mouse', 6, 20);

Output:

Examples of MySQL HAVING Clause

Example 1: Filtering Groups by Count

In this example we will the find products that have been sold more than once.

SELECT product, COUNT(*)
FROM sales
GROUP BY product
HAVING COUNT(*) > 1;

Output:

+---------+----------+
| product   | COUNT(*)  |
+---------+----------+
| Laptop     |        2          |
| Mouse     |        3          |
+---------+----------+

Explanation: Laptop appears twice in the table (Laptop with id 1 and 3) and Mouse appears three times in the table (Mouse with id 2, 4, and 7). Products that appear only once (like Keyboard and Headphones) are not included in the result.

Example 2: Filtering Groups by Sum

Here we will find products with a total quantity sold greater than 10:

SELECT product, SUM(quantity)
FROM sales
GROUP BY product
HAVING SUM(quantity) > 10;

Output:

+---------+---------------+
| product | SUM(quantity)     |
+---------+---------------+
| Mouse   |            22                |
+---------+---------------+

Explanation: Total quantity of mouse sold is 10 + 6 + 6 = 22, which is greater than 10. Other products (e.g., Laptop, Keyboard, Headphones) have total quantities that do not exceed 10, so they are excluded from the result.

Conclusion

The HAVING clause in MySQL is great for filtering grouped data after using the GROUP BY clause. This help us to get more specific results from data. We've covered the basics and provided some simple examples to help you understand how to use the HAVING clause in your own queries.

yuvrajghule281

Follow

Improve

Article Tags :

• Databases
• MySQL