The difference between the HAVING Clause and GROUP BY statement is common question asked in the database interviews. Both HAVING and GROUP BY are used as an extension of SQL queries. These extended SQL queries are used to filter the rows and return a particular value. HAVING clause and GROUP BY statements play an important role in querying the database.
- Read: More Interview Questions
How GROUP BY Statements work?
GROUP BY statements generally work on rows returned by the previous step. GROUP BY statements are used to return identical rows in a distinct table. The identical rows in the table are summarized by using aggregate functions present in the SELECT list. Functions like SUM(), MIN(), MAX () and AVG() are called as aggregate functions.
How does HAVING Clause work?
HAVING work as filter on the Group of rows returned by the second step in a SQL query. Most amatuer SQL believe that HAVING clause can be replaced by WHERE clause but this idea is wrong and will result in an error.
Example SQL for HAVING Clause and GROUP BY Statements.
Consider that we have a simple SQL query, like the statement shown below:
SELECT *
FROM [Sales].[SalesOrderHeader ]
The table for the above query contains the list of customers and their sale orders.
GROUP BY Statement
Using the GROUP BY statement customer list along with their orders can be grouped using the SQL query shown below:
SELECT CustomerID, COUNT(*) AS OrderNos
FROM [Sales] . [SalesOrderHeader]
WHERE OrderDate >= ‘2014-01-01 00:00:00.000’
AND OrderDate < ‘2015-01-01 00:00:00.000’
GROUP BY CustomerID
The above query will list the entire customer records stored in the database. If you want to display some specific records then HAVING clause will come to your rescue.
HAVING Clause
Just like mentioned in our HAVING clause definition it is used to filter the unwanted records from the result displayed by GROUP BY statement. HAVING clause will work similar to the WHERE clause used in the SQL statements.
SELECT CustomerID, COUNT (*) AS ORderNos
FROM [Sales] . [SalesOrderHeader]
WHERE OrderDate .= ‘2014-01-01 00:00:00:.000’
AND OrderDate < ‘2015-01-01 00:00:00.000’
GROUP BY CustomerID
HAVING COUNT (*) > 10
Using this clause the record search in SQL can be narrowed to minimum.
Difference Between HAVING Clause and GROUP BY Statements?
- Aggregate functions can be used in GROUP BY Statements.
- HAVING clause will work only if you use GROUP BY statement in your SQL query.
- HAVING clause can refer to any field present in the SELECT statement.
- HAVING clause also accepts fields mentioned in the GROUP BY statement. Even if the particular field is not mentioned in the SELECT statement.
- HAVING Clause cannot be used along with aggregate functions.
- HAVING clause can be used only if you have SELECT statement in your SQL query.
- HAVING Clause work like a search for GROUP BY statement.