w3resource

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 Expression: COUNT() with HAVING.

Relational Algebra Tree:

Relational Algebra Tree: COUNT() with HAVING.

Output:

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

Pictorial Presentation :

SQL COUNT with HAVING

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 Expression: COUNT() with having and group by.

Relational Algebra Tree:

Relational Algebra Tree: COUNT() with having and group by.

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.

SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS

 





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