Group by clause is used to Group the records into a set of summary rows by one or more columns or expression. Group by is used when are using an aggregate function in our query. Aggregate function (SUM, COUNT, AVG, MIN etc).

In order to demonstrate the Group By clause we will take the example of below records or table Order:

So if we want the sum of GrossTotal of all order and by StoreId, so how we can achieve this. We can do the same using GroupBy clause.

SELECT STOREID,SUM(GROSSTOTAL)
as TotalGrossAll FROM [ORDER] GROUP
BY STOREID

Output:

Group by multiple columns, now I want to Show sum of Gross Total by StoreId and OrderCreatedDate.

SELECT
STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
as
DATE,
SUM(GROSSTOTAL)
AS
TOTALGROSSALL
FROM
[ORDER]

GROUP
BY
STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)

Output:

Multiple Aggregate functions in a query, let say I want to calculate the count of no of order to a particular store based on Date and Sum of GrossAll.

SELECT STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
AS
DATE,
SUM(GROSSTOTAL)
AS TOTALGROSSALL,COUNT(STOREID) No_Of_Orders FROM [ORDER]
GROUP
BY STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)

It is instructed to use specific column name rather than using count(*) in the query.

Applying filter on the Group by result using where clause as shown below:

SELECT STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
AS
DATE,
SUM(GROSSTOTAL)
AS TOTALGROSSALL,COUNT(STOREID) No_Of_Orders FROM [ORDER]

where STOREID=‘3267’
GROUP
BY STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)

We can also use HAVING statement to filter the records, the best advantage of HAVING clause is to use an aggregate function in filter i.e. we can use the aggregate function as a condition which is not possible in where clause. Having clause has to come after the group by clause.

SELECT STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
AS
DATE,
SUM(GROSSTOTAL)
AS TOTALGROSSALL,COUNT(STOREID) No_Of_Orders FROM [ORDER]

GROUP
BY STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
HAVING
COUNT(STOREID)
>6

In WHERE clause the records are the first filterd and than applied aggregation, while in HAVING clause they are first aggregated and than filtered.

Having clause can only be used with the select statement while Where clause can be used with Update, Insert, delete, Select.