HAVING COUNT 

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:

1. SELECT column1, column2, column3, COUNT(column4): This selects the specified columns along with the count of column4. The COUNT(column4) function returns the number of non-null values in column4.
2. FROM table_name: This specifies the table from which the data is retrieved, in this case, table_name.
3. GROUP BY column1, column2, column3: This groups the results by the specified columns (column1, column2, and column3). It means that the count of column4 will be calculated for each unique combination of values in column1, column2, and column3.
4. HAVING COUNT(column4): This is a filtering condition applied after the grouping. It filters the grouped results based on the count of column4. Since no specific condition is provided after COUNT(column4), it's implicitly checking for non-zero counts. This means it will only return groups where COUNT(column4) is not zero.

SELECT 
  column1, 
  column2, 
  column3, 
  COUNT(column4) as Total
FROM 
  table_name 
GROUP BY
 column1, column2, column3
HAVING
   Total

The reason is that MySQL evaluates the HAVING clause before the SELECT clause. (FWGHSDOL)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.