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
SQL: Tips of the Day
Is SQL syntax case sensitive?
The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, etc), but are often written in all caps. However in some setups table and column names are case-sensitive. MySQL has a configuration option to enable/disable it. Usually case-sensitive table and column names are the default on Linux MySQL and case-insensitive used to be the default on Windows, but now the installer asked about this during setup. For MSSQL it is a function of the database's collation setting.
Ref: https://bit.ly/3R2iyNZ
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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