• Home
• SQL Home
• SQL Functions
• SQL Aggregate functions
• ▼COUNT Function
• COUNT Function
• COUNT with Distinct
• COUNT with Group by
• COUNT Having and Group by
• ▼Other Aggregate Functions
• SUM Function
• AVG Function
• MAX Function
• MIN Function

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:

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
Follow us on Facebook
and Twitter for latest update.
where A.Date >= Convert(datetime, '2010-04-01' )
In your query, 2010-4-01 is treated as a mathematical expression, so in essence it read
select *  
from dbo.March2010 A 
where A.Date >= 2005;
Database: SQL Server
Ref: https://bit.ly/3zRzWzx
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us
at Facebook