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.
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.