SQL COUNT() with HAVING

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:

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:

SELECT COUNT( * ) 
FROM agents 
HAVING COUNT(*)>3;

Sample table : agents

Relational Algebra Expression:

Relational Algebra Tree:

Output:

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

Pictorial Presentation :

SQL COUNT( ) with having and group by

Sample table: agents

To get data of 'commission' and number of agents for that commission from the 'agents' table with the following conditions -

1. number of agents for a particular 'commisson',

2. number of agents for that particular 'commission' must be more than 3,

the following SQL statement can be used :

SELECT commission, COUNT (*) 
FROM agents 
GROUP BY commission 
HAVING COUNT(*)>3;

Relational Algebra Expression:

Relational Algebra Tree:

Output:

COMMISSION   COUNT(*)
---------- ----------
       .15          4

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Here is a slide presentation of all aggregate functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: COUNT with Group by
Next: SUM function