How Group By Clause is used with Having Clause?
Having clause is used to filter values in Group By clause. We will see how this works with the help of an example. The below query filters out some of the rows
Syntax
SELECT column1,column2
from table_name
GROUP BY column1,column2 HAVING condition;
Example:
SELECT SalesOrderID,
SUM(UnitPrice* OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING TotalPrice > 5000
Since the WHERE clause’s visibility is one row at a time, there isn’t a way for it to evaluate the SUM across all SalesOrderID’s. The HAVING clause is evaluated after the grouping is created. You can use the ‘Where’ clause with the ‘Having’ clause as well. The WHERE clause is applied first to the individual rows in the tables. Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set.
Example:
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
WHERE SalesOrderID > 500
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 10000
So here, the having clause will be applied to the rows that are filtered by the where clause. Having a clause can only compare the results of aggregated functions or column part of the group by.
Difference between Where and Group By
Where and Group By clauses are used to filter rows returned by the query based on the condition. In this article, we will discuss Where Clause and Group By Clause as well as we will discuss Differences Between Where and Group By Clause. Before proceeding to that, let’s first discuss the Use Case Statement.
Use Case: Suppose some sales company wants to get a list of Customers who bought some number of items last year so that they can sell more stuff to them this year. There is a table called SalesOrder with columns CustomerId, SalesOrderId, Order_Date, OrderNumber, OrderItem, UnitPrice, and OrderQty Now we need to get the customers who made orders last year i.e. 2017.
Contact Us