MySQL HAVING COUNT

Summary: in this tutorial, you will learn how to use MySQL HAVING COUNT to filter groups based on the number of items in each group.

Introduction to MySQL HAVING COUNT

In MySQL, the GROUP BY clause organizes rows into groups. When you combine the GROUP BY clause with the COUNT function, you will get both the groups and the number of items in each group.

To filter the groups based on the number of items in each group, you use the HAVING clause and the COUNT function.

The following illustrates the basic syntax for using the HAVING clause with the COUNT function to filter groups:

SELECT 
  c1, 
  COUNT(c2) 
FROM 
  table_1 
GROUP BY 
  c1
HAVING 
  COUNT(c2)...Code language: SQL (Structured Query Language) (sql)

Note that you cannot assign an alias to the COUNT(column_2) in the SELECT clause and use the column alias in the HAVING clause like this:

SELECT 
  c1, 
  COUNT(c2) count_c2
FROM 
  table_1 
GROUP BY 
  c1
HAVING 
  count_c2...Code language: SQL (Structured Query Language) (sql)

The reason is that MySQL evaluates the HAVING clause before the SELECT clause. Therefore, at the time MySQL evaluated the HAVING clause, it doesn’t know the column alias count_c2 because it has not evaluated the SELECT clause yet.

MySQL HAVING COUNT example

We’ll take some examples of using the HAVING COUNT clause

1) Simple HAVING COUNT example

First, create a new table of sales that has three columns sale_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)
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the sales table:

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);Code language: SQL (Structured Query Language) (sql)

Third, count the number of sales per product:

SELECT product_name, COUNT(id)
FROM sales
GROUP BY product_name;Code language: SQL (Structured Query Language) (sql)

The query shows the product names and the number of sales for each product:

+--------------+-----------+
| product_name | COUNT(id) |
+--------------+-----------+
| Product A    |         2 |
| Product B    |         2 |
| Product C    |         1 |
+--------------+-----------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

To find which product had one sale, you use the HAVING clause with the COUNT function as follows:

SELECT 
  product_name, 
  COUNT(id) 
FROM 
  sales 
GROUP BY 
  product_name 
HAVING 
  COUNT(id) = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------+-----------+
| product_name | COUNT(id) |
+--------------+-----------+
| Product C    |         1 |
+--------------+-----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Similarly, you can find the products that had more than one sale by using the operator > in the HAVING clause:

SELECT 
  product_name, 
  COUNT(id) 
FROM 
  sales 
GROUP BY 
  product_name 
HAVING 
  COUNT(id) > 1;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------+-----------+
| product_name | COUNT(id) |
+--------------+-----------+
| Product A    |         2 |
| Product B    |         2 |
+--------------+-----------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Practical HAVING with COUNT example

We’ll use the table customers and orders tables from the sample database:

The following query uses the HAVING clause with the COUNT function to get the customers who placed more than four orders:

SELECT 
  customerName, 
  COUNT(*) order_count 
FROM 
  orders 
  INNER JOIN customers using (customerNumber) 
GROUP BY 
  customerName 
HAVING 
  COUNT(*) > 4 
ORDER BY 
  order_count;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------+-------------+
| 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 |
+------------------------------+-------------+
7 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the HAVING COUNT clause to filter groups by the number of items in each group.