Difficulty
Last Reviewed
Multi-select
Completed
Notes
Reference Link
Reviewed
Status
Done
Tags
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 COUNTfunction.
The following illustrates the basic syntax for using the HAVING clause with the COUNT function to filter groups:
SELECT
column1,
column2,
column3,
COUNT(column4)
FROM
table_name
GROUP BY
column1, column2, column3
HAVING
COUNT(column4) = condition This query aims to select column1, column2, and column3 from the table table_name, along with the count of column4. It groups the results by column1, column2, and column3, and then applies a filter using the HAVING clause based on the count of column4.
Explanation:
SELECT column1, column2, column3, COUNT(column4): This selects the specified columns along with the count ofcolumn4. TheCOUNT(column4)function returns the number of non-null values incolumn4.FROM table_name: This specifies the table from which the data is retrieved, in this case,table_name.GROUP BY column1, column2, column3: This groups the results by the specified columns (column1,column2, andcolumn3). It means that the count ofcolumn4will be calculated for each unique combination of values incolumn1,column2, andcolumn3.HAVING COUNT(column4): This is a filtering condition applied after the grouping. It filters the grouped results based on the count ofcolumn4. Since no specific condition is provided afterCOUNT(column4), it's implicitly checking for non-zero counts. This means it will only return groups whereCOUNT(column4)is not zero.
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
column1,
column2,
column3,
COUNT(column4) as Total
FROM
table_name
GROUP BY
column1, column2, column3
HAVING
TotalThe reason is that MySQL evaluates theHAVINGclause before theSELECTclause. (FWGHSDOL)Therefore, at the time MySQL evaluated theHAVINGclause, it doesn’t know the column aliascount_c2because it has not evaluated theSELECTclause yet.
‣
Example: 1
‣
Example: 2
‣