{"id":10846,"date":"2023-10-09T06:05:44","date_gmt":"2023-10-09T13:05:44","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=10846"},"modified":"2023-10-09T06:05:45","modified_gmt":"2023-10-09T13:05:45","slug":"mysql-having-count","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-having-count\/","title":{"rendered":"MySQL HAVING COUNT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL HAVING COUNT<\/code> to filter groups based on the number of items in each group.<\/p>\n\n\n\n

Introduction to MySQL HAVING COUNT<\/h2>\n\n\n\n

In MySQL, the GROUP BY<\/a> clause organizes rows into groups. When you combine the GROUP BY<\/code> clause with the COUNT<\/a> function, you will get both the groups and the number of items in each group.<\/p>\n\n\n\n

To filter the groups based on the number of items in each group, you use the HAVING<\/a> clause and the COUNT<\/code> function.<\/p>\n\n\n\n

The following illustrates the basic syntax for using the HAVING<\/code> clause with the COUNT<\/code> function to filter groups:<\/p>\n\n\n

SELECT<\/span> \n  c1, \n  COUNT<\/span>(c2) \nFROM<\/span> \n  table_1 \nGROUP<\/span> BY<\/span> \n  c1\nHAVING<\/span> \n  COUNT<\/span>(c2)...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you cannot assign an alias to the COUNT(column_2)<\/code> in the SELECT<\/code> clause and use the column alias in the HAVING<\/code> clause like this:<\/p>\n\n\n
SELECT<\/span> \n  c1, \n  COUNT<\/span>(c2) count_c2\nFROM<\/span> \n  table_1 \nGROUP<\/span> BY<\/span> \n  c1\nHAVING<\/span> \n  count_c2...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The reason is that MySQL evaluates the HAVING<\/code> clause before the SELECT<\/code> clause. Therefore, at the time MySQL evaluated the HAVING<\/code> clause, it doesn’t know the column alias count_c2<\/code> because it has not evaluated the SELECT<\/code> clause yet.<\/p>\n\n\n\n
MySQL HAVING COUNT example<\/h2>\n\n\n\n
We’ll take some examples of using the HAVING<\/code> COUNT<\/code> clause<\/p>\n\n\n\n
1) Simple HAVING COUNT example<\/h3>\n\n\n\n
First, create a new table of sales that has three columns sale_id<\/code>, product_name<\/code>, and sale_amount<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    product_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    sale_amount DECIMAL<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert some rows into the sales table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> sales (product_name, sale_amount)\nVALUES<\/span>\n    ('Product A'<\/span>, 100.50<\/span>),\n    ('Product B'<\/span>, 75.25<\/span>),\n    ('Product A'<\/span>, 120.75<\/span>),\n    ('Product C'<\/span>, 50.00<\/span>),\n    ('Product B'<\/span>, 90.80<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, count the number of sales per product:<\/p>\n\n\n
SELECT<\/span> product_name, COUNT<\/span>(id<\/span>)\nFROM<\/span> sales\nGROUP<\/span> BY<\/span> product_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query shows the product names and the number of sales for each product:<\/p>\n\n\n
+--------------+-----------+<\/span>\n| product_name | COUNT(id) |\n+--------------+-----------+<\/span>\n| Product A    |         2 |\n| Product B    |         2 |\n| Product C    |         1 |\n+--------------+-----------+<\/span>\n3 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To find which product had one sale, you use the HAVING<\/code> clause with the COUNT<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span> \n  product_name, \n  COUNT<\/span>(id<\/span>) \nFROM<\/span> \n  sales \nGROUP<\/span> BY<\/span> \n  product_name \nHAVING<\/span> \n  COUNT<\/span>(id<\/span>) = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------+-----------+<\/span>\n| product_name | COUNT(id) |\n+--------------+-----------+<\/span>\n| Product C    |         1 |\n+--------------+-----------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Similarly, you can find the products that had more than one sale by using the operator ><\/code> in the HAVING<\/code> clause:<\/p>\n\n\n
SELECT<\/span> \n  product_name, \n  COUNT<\/span>(id<\/span>) \nFROM<\/span> \n  sales \nGROUP<\/span> BY<\/span> \n  product_name \nHAVING<\/span> \n  COUNT<\/span>(id<\/span>) > 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------+-----------+<\/span>\n| product_name | COUNT(id) |\n+--------------+-----------+<\/span>\n| Product A    |         2 |\n| Product B    |         2 |\n+--------------+-----------+<\/span>\n2 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Practical HAVING with COUNT example<\/h3>\n\n\n\n
We’ll use the table customers<\/code> and orders<\/code> tables from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following query uses the HAVING<\/code> clause with the COUNT<\/code> function to get the customers who placed more than four orders:<\/p>\n\n\n
SELECT<\/span> \n  customerName, \n  COUNT<\/span>(*) order_count \nFROM<\/span> \n  orders \n  INNER<\/span> JOIN<\/span> customers using<\/span> (customerNumber) \nGROUP<\/span> BY<\/span> \n  customerName \nHAVING<\/span> \n  COUNT<\/span>(*) > 4<\/span> \nORDER<\/span> BY<\/span> \n  order_count;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------------------+-------------+<\/span>\n| customerName                 | order_count |\n+------------------------------+-------------+<\/span>\n| Australian Collectors, Co.   |           5 |\n| Danish Wholesale Imports     |           5 |\n| Dragon Souveniers, Ltd.      |           5 |\n| Down Under Souveniers, Inc   |           5 |\n| Reims Collectables           |           5 |\n| Mini Gifts Distributors Ltd. |          17 |\n| Euro+ Shopping Channel       |          26 |\n+------------------------------+-------------+<\/span>\n7 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the HAVING COUNT<\/code> clause to filter groups by the number of items in each group.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t