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.