SQL COUNT() with HAVING
COUNT() with HAVING
The HAVING clause with the SQL COUNT() function is used to set a condition with the SELECT statement. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause filters groups after the GROUP BY operation.
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.
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 where the number of agents must be greater than 3, the following SQL statement can be used:
SQL Code:
-- 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 and group by
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 the commission and the number of agents for each commission where the number of agents is more than 3,
the following SQL statement can be used :
SQL Code:
-- Selecting the 'commission' column and counting the number of occurrences for each distinct value
SELECT commission, COUNT(*)
-- From the 'agents' table
FROM agents
-- Grouping the results by the 'commission' column
GROUP BY commission
-- 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 commission, COUNT(*): This is the main part of the SQL query. It selects the 'commission' column from the 'agents' table and counts the number of occurrences of each distinct value in the 'commission' column using the COUNT(*) function. The result will include two columns: 'commission' and the count of occurrences.
- 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.
- GROUP BY commission: This clause groups the result set by the 'commission' column. The GROUP BY clause is used with aggregate functions like COUNT() to divide the rows returned from the SELECT statement into groups based on the values in one or more columns. In this case, it groups the rows based on the values in the 'commission' column.
- 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:
COMMISSION COUNT(*)
---------- ----------
.15 4
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
Common Errors:
- Incorrect Use of HAVING: Misplacing the HAVING clause before GROUP BY.
- Mixing WHERE and HAVING: Understanding the difference between WHERE and HAVING.
Advanced Usage:
- Combining Multiple Aggregate Functions: Using SUM(), AVG(), etc., with HAVING.
- Complex Conditions: Using logical operators in HAVING conditions.
Frequently Asked Questions (FAQ) - SQL COUNT() with HAVING
1. What is the purpose of the HAVING clause in SQL?
The HAVING clause is used to filter groups of rows after the GROUP BY operation, based on a specified condition involving aggregate functions.
2. How does SQL HAVING differ from WHERE in SQL?
The WHERE clause filters individual rows before grouping, while the HAVING clause filters groups of rows after the grouping is performed.
3. What is the relationship between COUNT() and HAVING in SQL?
The COUNT() function can be used with the HAVING clause to filter groups of rows based on the count of rows in each group.
4. Can HAVING be used without GROUP BY in SQL?
Yes, HAVING can be used without GROUP BY, but it’s typically used to filter groups of data created by GROUP BY.
5. What is the general syntax for using SQL COUNT() with HAVING?
The general syntax involves a SELECT statement with COUNT(), a FROM clause specifying the table, a GROUP BY clause (if applicable), and a HAVING clause with the condition based on COUNT().
6. What are common errors when using HAVING with SQL COUNT()?
- Misplacing the HAVING clause before GROUP BY.
- Confusing the WHERE clause with the HAVING clause.
7. Can SQL HAVING be used with other aggregate functions?
Yes, HAVING can be used with other aggregate functions like SUM(), AVG(), MAX(), and MIN().
8. What advanced usages of HAVING with SQL COUNT() are there?
Advanced usages include combining multiple aggregate functions in the HAVING clause and using complex conditions with logical operators.
9. What are some best practices for using HAVING with SQL COUNT()?
- Ensure correct placement of HAVING after GROUP BY.
- Use HAVING for filtering aggregate data and WHERE for filtering individual rows.
- Combine HAVING with other aggregate functions for more complex queries.
10. Is HAVING supported in all SQL databases?
Yes, the HAVING clause is supported in all standard SQL databases, though syntax and specific features might vary slightly.
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