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
You may also like
