Open In App

Difference between Where and Group By

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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.

The WHERE Clause

The WHERE Clause generally is used to filter the rows from a table on a specific condition. WHERE clause specifies search conditions for the rows returned by the Query and limits rows to a specific row set. If a table has a huge amount of records and if someone wants to get the particular records then using the ‘where’ clause is useful. The Syntax of the WHERE Clause is

Syntax

SELECT column1,column2,cloumn3….

FORM table_name

WHERE Condition;

Example:

SELECT * 
FROM [Sales].[Orders]
WHERE Order_Date >= '2017-01-01 00:00:00.000'
AND Order_Date < '2018-01-01 00:00:00.000' 

This will return the row set with all the Customers and corresponding Orders for the year 2017. 

The GROUP BY Clause

Group by clause is used to group rows by one or more columns . Group by Clause is used with aggregate functions like SUM(), AVG(), MIN() etc. GROUP BY clause summaries identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), AVG(), SUM(), MIN(), MAX(), etc. The Syntax of the GROUP BY Clause is

Syntax

SELECT column1,column2

from table_name

GROUP BY column1,column2 ;

Example:

SELECT CustomerID, COUNT(*) AS OrderNumbers
FROM [Sales].[Orders]
WHERE Order_Date >= '2017-01-01 00:00:00.000'
AND Order_Date < '2018-01-01 00:00:00.000'
GROUP BY CustomerId 

This will return the row set of the Customers (CustomerId) who made orders in the year 2017 and the total count of orders each Customer made.

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 Clause

Where Clause

Group by Clause

Where Clause is generally used to filter the rows from a table based on a specific condition.

Group by clause is used to group rows by one or more columns.

It does not involve an aggregate function.

It involves aggregations functions like SUM(), AVG(), COUNT(),MAX() etc.

Where clause is used with select, update, and with delete statements.

It is used only with select statements.

Its affected individual rows.

It affects the whole group of rows to perform aggregate functions.

It does not involve having clause. It involves having clause.

Conclusion

In this article, we have concluded that WHERE is used to filter records before any groupings take place that is on single rows, GROUP BY aggregates/ groups the rows and returns the summary for each group and HAVING is used to filter values after they have been grouped.


Last Updated : 01 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads