SQL Server – Using GROUP BY

SQL Server – Using GROUP BY statement:

Group by statement in SQL server database groups the data into groups using column name specified in the select query. Group by statement can be used with one or more columns. Group by query is mostly used with aggregate functions.

Syntax of Group By query:

Select columnName1, columnName2 from tableName group by columnName1, columnName2

Group By query is useful in identifying or grouping data with similar values in a large set of data. This can be used in creating customized reports and multiple other purposes. Group By query is usually used with Aggregate functions like min, max, count, avg, etc.

Example of Using Group By query:

Let’s consider we have a order table in our SQL database. We have to identify and display number of unique customer who have ordered products:

select CustomerID from Orders group by CustomerId

Now this is similar to DISTINCT SQL clause. But there are differences between Distinct and Group By in SQL Database.

SQL Distinct v/s SQL Group By:

NULL Values: Distinct query will return null values in result set whereas Group By will eliminate NULL values.

Aggregate functions: Distinct clause can’t be used with SQL aggregate functions whereas Group By clause is often used by Aggregate functions.

Using on multiple columns:

Suppose we want to group Orders by CustomerId and delivery PostalCode to create a report resultset. We can use below query for that:

select CustomerID,ShipPostalCode from Orders group by CustomerId,ShipPostalCode

If you add a column which is not specified in the group by clause it will return an error. Example:

select CustomerID,ShipPostalCode,EmployeeId from Orders group by CustomerId,ShipPostalCode

Error:

Column ‘Orders.EmployeeID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SQL Group by only works with column name specified in the group by clause or with aggregate functions if column name is not specified.

SQL Group by Aggregate function example:

Suppose we have to find out number of different products ordered by customers with discount applied on total final cost. Here we can use below query:

Select ProductId,UnitPrice,sum(Quantity) as 'Total Quantity',Discount, 
(sum(Quantity) * UnitPrice ) as 'Total Before Discount',
(sum(Quantity) * UnitPrice *(1-Discount)) as 'Total After Discount'
from [Order Details] 
group by ProductId,UnitPrice,Discount
order by ProductId asc

Here we can also apply SQL JOIN to the product table to identity product against ProductId.

With JOIN:

For using JOIN with Group By clause we will have to specify the column name from the other in the group by clause which we will use in our query. Example:

Select o.ProductId,p.ProductName,o.UnitPrice,sum(o.Quantity) as 'Total Quantity',o.Discount, 
(sum(o.Quantity) * o.UnitPrice ) as 'Total Before Discount',
(sum(o.Quantity) * o.UnitPrice *(1-o.Discount)) as 'Total After Discount'
from [Order Details] o
left outer join Products p on o.ProductID = p.ProductID
group by o.ProductId,o.UnitPrice,o.Discount,p.ProductName
order by o.ProductId asc

This example uses NorthWind database.