Introduction to MySQL HAVING clause

The HAVING is use along with the GROUP BY clause to filter the group based on a specified condition.

SELECT 
  column1, 
  column2, 
  column3, 
  aggregate_function(column4)
FROM 
  table_name 
WHERE 
  condition_statement
GROUP BY
 column1, column2, column3
HAVING
  group_conditions;

• Note that the HAVING clause only becomes operational after the the GROUP BY returns a value, it is the value return by the GROUP BY that the HAVING filter according to the instruction. The HAVING clause applies the condition to groups of rows, while the WHERE clause applies the condition to individual rows.
• Note that if you omit the GROUP BY clause in a syntax, the HAVING clause will behaves like a WHERE clause.

MySQL evaluates the HAVING clause after the FROM, WHERE, SELECT and GROUP BYclauses, but before DISTINCT, SELECT, ORDER BY, and LIMIT clauses:

Note that the SQL standard specifies that the HAVING is evaluated before SELECT clause and after GROUP BY clause.

MySQL HAVING clause examples

MySQL HAVING COUNT

Overview

In MySQL, when you need to group rows and then filter those groups based on an aggregate calculation (like counting the number of rows in each group), you use the combination of the GROUP BY clause with an aggregate function (such as COUNT()) and then filter the resulting groups with the HAVING clause. The key difference between WHERE and HAVING is that WHERE filters rows before grouping, while HAVING filters groups after aggregation.

How It Works

Grouping Data

• GROUP BY: This clause arranges your rows into groups based on one or more columns. For example, if you have a table of sales, you can group the data by product name so that each group contains all sales for a specific product.

Aggregating Data

• COUNT() Function: This function tallies the number of rows within each group. When combined with GROUP BY, it lets you see how many entries exist in each group.

Filtering Groups

• HAVING Clause: After the data is grouped and the aggregate functions (like COUNT()) are calculated, the HAVINGclause is used to filter out groups that do not meet a certain condition. For example, you might want to display only those groups where the count is greater than a certain threshold.

Important Note:

In MySQL, you cannot use an alias defined in the SELECT clause (e.g., an alias for COUNT(c2)) within the HAVING clause because the HAVING clause is executed before the SELECT clause is processed. This means that the alias isn’t recognized when MySQL evaluates the HAVING condition.

Example 1: A Simple HAVING COUNT Query

Step 1: Creating a Sales Table

First, create a table named sales with three columns: id, product_name, and sale_amount.

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    product_name VARCHAR(50) NOT NULL,
    sale_amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(id)
);

Step 2: Inserting Data into the Table

Next, add several rows into the sales table:

sql
Copy
INSERT INTO sales (product_name, sale_amount)
VALUES
    ('Product A', 100.50),
    ('Product B', 75.25),
    ('Product A', 120.75),
    ('Product C', 50.00),
    ('Product B', 90.80);

Step 3: Counting Sales per Product

Now, to see how many sales each product has, group the records by product_name and count the number of sales (using the id column):

SELECT product_name, COUNT(id)
FROM sales
GROUP BY product_name;

Expected Output:

+--------------+-----------+
| product_name | COUNT(id) |
+--------------+-----------+
| Product A    |         2 |
| Product B    |         2 |
| Product C    |         1 |
+--------------+-----------+

Step 4: Filtering Groups with HAVING

To filter out the groups based on the count of sales, you use the HAVING clause. For instance, to retrieve the product(s) with only one sale:

SELECT product_name, COUNT(id)
FROM sales
GROUP BY product_name
HAVING COUNT(id) = 1;

Expected Output:

+--------------+-----------+
| product_name | COUNT(id) |
+--------------+-----------+
| Product C    |         1 |
+--------------+-----------+

Likewise, if you want to display products that have more than one sale:

SELECT product_name, COUNT(id)
FROM sales
GROUP BY product_name
HAVING COUNT(id) > 1;

Expected Output:

+--------------+-----------+
| product_name | COUNT(id) |
+--------------+-----------+
| Product A    |         2 |
| Product B    |         2 |
+--------------+-----------+

Example 2: Practical HAVING with COUNT

Imagine you have two tables, customers and orders, in a sample database. Suppose you want to find all customers who placed more than four orders. Here’s how you can do it:

SELECT
  customerName,
  COUNT(*) AS order_count
FROM orders
INNER JOIN customers 
USING (customerNumber)
GROUP BY customerName
HAVING COUNT(*) > 4
ORDER BY order_count;

Explanation:

• INNER JOIN: This joins the orders and customers tables on the customerNumber column.
• GROUP BY customerName: This groups the results by each customer's name.
• COUNT(*) AS order_count: This calculates the total number of orders for each customer. (Notice that even though we assign an alias order_count in the SELECT clause, you must use the actual aggregate function COUNT(*)in the HAVING clause.)
• HAVING COUNT(*) > 4: This filters out any customer group that has 4 or fewer orders.
• ORDER BY order_count: This orders the resulting groups by the number of orders.

Expected Output:

diff
Copy
+------------------------------+-------------+
| customerName                 | order_count |
+------------------------------+-------------+
| Australian Collectors, Co.   |           5 |
| Danish Wholesale Imports     |           5 |
| Dragon Souveniers, Ltd.      |           5 |
| Down Under Souveniers, Inc   |           5 |
| Reims Collectables           |           5 |
| Mini Gifts Distributors Ltd. |          17 |
| Euro+ Shopping Channel       |          26 |
+------------------------------+-------------+

Key Takeaways

• GROUP BY and Aggregation: The GROUP BY clause organizes rows into groups, and functions like COUNT()calculate aggregate values for each group.
• HAVING vs. WHERE: The HAVING clause filters groups after aggregation, unlike the WHERE clause which filters individual rows before grouping.
• Alias Limitation: You cannot use an alias from the SELECT clause in the HAVING clause because of the order in which MySQL processes the query.

This detailed explanation and rewritten tutorial should help you understand how to use the HAVING clause along with the COUNT function to filter groups in MySQL.