SQL COUNT() with HAVING

Last update on April 20 2024 12:33:10 (UTC/GMT +8 hours)

COUNT() with HAVING

The HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. The HAVING clause is used instead of WHERE clause with SQL COUNT() function.

The GROUP BY with HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.

Example:

Sample table : agents

 
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get data of number of agents from the 'agents' table with the following condition -

1. number of agents must be greater than 3,

the following SQL statement can be used:

-- Counting the total number of rows in the 'agents' table
SELECT COUNT(*)
-- From the 'agents' table
FROM agents
-- Having clause filters the results based on the aggregate condition
-- Counting the number of occurrences and including only those with a count greater than 3
HAVING COUNT(*) > 3;

Explanation:

SELECT COUNT(*): This is the main part of the SQL query. It selects the count of all rows from the 'agents' table using the COUNT(*) function. The result will be a single row with a single column containing the total number of rows in the 'agents' table.
FROM agents: This specifies the source of the data for the query, which is the 'agents' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'agents' table.
HAVING COUNT(*) > 3: This clause filters the groups formed by the GROUP BY clause based on an aggregate condition. The HAVING clause is similar to the WHERE clause, but it is used with aggregate functions like COUNT(). Here, it filters the groups where the count of rows in each group is greater than 3.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

  COUNT(*)
----------
        12

Visual Presentation :

SQL COUNT with HAVING