SQL GROUP BY Clause

The GROUP BY clause is used to summarize data based on grouping criteria. For each group one column is returned in result set.

Syntax

SELECT expression1, expression2, … expressionN, aggregate_function (aggregate_expression)

FROM TableName

WHERE conditions

GROUP BY expression1, expression2, … expressionN;

In this example we will display the total for each SalesOrderID column from table SalesOrderDetail of database AdventureWorks2014, the query is given below.

SELECT SalesOrderID, SUM(LineTotal) AS Total, ModifiedDate
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, ModifiedDate
ORDER BY SalesOrderID;

All columns in SELECT Statement must be included in the GROUP BY clause, otherwise you’ll get an error. As shown below, we have failed to include the column ModifiedDate.

The GROUP BY clause is often used with an aggregate function eg. SUM, MIN, AVG, MAX, COUNT etc.

There is another example with COUNT function, here we would like to see how many employees are there for each JobTitle in descending order (see ORDER BY Clause) as given below.

SELECT JobTitle, COUNT(*) AS Number
FROM [HumanResources].[Employee]
GROUP BY JobTitle
ORDER BY Number DESC;

Here is another example with MAX function, here we would like to see the maximum StandardCost by ProductNumber in descending order (see order by clause) as given below.

SELECT ProductNumber, MAX(StandardCost) AS StandardCost
FROM [Production].[Product]
GROUP BY ProductNumber
ORDER BY StandardCost DESC;

CLICK HERE to watch live practical.

Reference: www.SQLServerLog.com