SELECT SQL GROUP BY statement
The SQL GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The SQL GROUP BY returns only one result per group of data the GROUP BY clause always follows the where clause and the GROUP BY clause always precedes the ORDER BY statement
A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group.
Important points
- GROUP BY statement is used with the SELECT statement
- In the query, the GROUP BY statement is placed after the WHERE clause
- In the query, the GROUP BY clause is placed before the ORDER BY clause if used any
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
This clause works with the select specific list of items, and we can use HAVING, and ORDER BY clauses. group by clause always works with an aggregate function like MAX, MIN, SUM, AVG, COUNT
Syntax
Create table CarSale
(
Id int primary key identity(1,1) ,
Model varchar(200),
Price money
)
Insert into CarSale values('Baleno', 900000),('M551', 950000),('M551', 950000),('L551', 950000),
('L538', 650000),('L538', 900000),('BWW', 8000000),('8530', 450000),('L530',380000), ('L545', 280000)
Example
![selectInsertTable]()
SQL Aggregate functions
MAX() function in SQL
This function returns the maximum value of the numeric column of the specified criteria.
Syntax
Select max(Price) As 'MaximumCostOfModel' from CarSale
Example
![SelectMaxStatement]()
MIN() function in SQL
This function returns the minimum of the numeric column of specified criteria
Syntax
Select Min(Price) As 'MinimumCostOfModel' from CarSale
Example
![SelectMin]()
SUM() function in SQL
This function returns the total sum of a numeric column of specified criteria.
Syntax
Select SUM(Price) As 'SumCostOfAllModel' from CarSale
Example
![SelectSumStatement]()
AVG() function in SQL
The AVG() function returns the average value of a numeric column of specified criteria.
Syntax
Select AVG(Price) As 'AverageCostOfModel' from CarSale
Example
![SelectAvg]()
COUNT() function in SQL
This function returns the number of rows that match the specified criteria.
Syntax
Select Count(Price) As 'TotalVehicleModels' from CarSale
Example
![SelectCount1]()
Distinct clause
The distinct clause is used to filter unique records out of the duplicate records that satisfy the query criteria
Syntax
Select Distinct(Model), Price from CarSale
Example
![DistrictSelect1]()
SELECT a simple GROUP BY clause
The SELECT GROUP BY statement lists the Employee of EmployeeDetails
Syntax
SELECT EmployeeName,EmployeeAddress as EmployeeCity
FROM EmployeeDetails
GROUP BY EmployeeName, EmployeeAddress ORDER BY EmployeeName;
Example
![SelectDetails]()
The SQL GROUP BY statement list the number of Employee in each EmployeeId, sorted from high to low
Syntax
SELECT COUNT(EmployeeId), EmployeeName
FROM EmployeeDetails
GROUP BY EmployeeName
ORDER BY COUNT(EmployeeID) DESC;
Example
![SelectEmployeeDetail]()
SELECT GROUP BY clause with multiple tables
The SELECT GROUP BY statement retrieves the number of employees for each EmployeeName from the EmployeeDetails table joined to the EmployeeDetail table
This example uses a sample.
Syntax
SELECT EmployeeName
FROM EmployeeDetails
INNER JOIN EmployeeDetail
ON EmployeeDetails.EmployeeID=EmployeeDetail.EmpId GROUP BY EmployeeName ORDER BY EmployeeName;
Example
![SelectInnerJoin]()
SELECT GROUP BY clause with an expression
The SELECT GROUP BY statement retrieves the total EmployeeDetails by using the function. The same expression must be present in both the SELECT list and the GROUP BY clause statement.
Syntax
SELECT COUNT(EmployeeID), EmployeeName
FROM EmployeeDetails
GROUP BY EmployeeName;
Example
![SelectGroupByStatement1]()
SELECT GROUP BY statement with a HAVING clause
The SELECT GROUP BY statement uses the HAVING clause to specify which of the groups generated in the GROUP BY clause should be included in the result set.
Syntax
SELECT COUNT(EmployeeID)EmployeeAdress
FROM EmployeeDetails
GROUP BY EmployeeName
HAVING COUNT(EmployeeID)> 1;
Example
![SelectHavingClouse]()
SELECT GROUP BY basic use statement
The SELECT GROUP BY clause finds the total EmployeeID, and EmployeeName for EmployeeDetails.
Syntax
SELECT COUNT(EmployeeID), EmployeeName from EmployeeDetails GROUP BY EmployeeName
Example
![SelectGroupByStatement]()
Basic use of the DISTRIBUTED_AGG hint
The SELECT the DISTRIBUTED_AGG query hint forces the appliance to shuffle the table on the EmployeeDetails column before performing the aggregation.
Syntax
SELECT COUNT(EmployeeID), EmployeeName , EmployeeAddress
FROM EmployeeDetails
GROUP BY EmployeeName WITH (DISTRIBUTED_AGG), EmployeeAddress
ORDER BY EmployeeName DESC
Example
![SelectDistributedAge]()
SELECT Variations for GROUP BY statement
The SELECT variations for the GROUP BY statement are the select list has no aggregations, and each column in the select list must be included in the GROUP BY list. computed columns in the select list can be listed, but are not required, in the GROUP BY list. These are examples of syntactically valid SELECT statements:
Syntax
SELECT EmployeeName, EmployeeAddress FROM EmployeeDetails GROUP BY EmployeeName, EmployeeAddress; SELECT EmpName FROM EmployeeDetail GROUP BY EmpName, EmpAddress;
Example
![SelectDetails]()
SELECT GROUP BY with multiple GROUP BY expressions
The following example groups result using multiple GROUP BY EmployeeName If, within each EmployeeId group
Syntax
SELECT COUNT(EmployeeId), EmployeeName
FROM EmployeeDetails
GROUP BY EmployeeName
ORDER BY COUNT(EmployeeID) DESC;
Example
![Select GroupByClouse]()
SELECT GROUP BY clause with a HAVING clause
The GROUP BY HAVING clause uses the clause to specify the groups generated in the GROUP BY clause that should be included in the result set
Syntax
SELECT COUNT(EmployeeId), EmployeeName
FROM EmployeeDetails
GROUP BY EmployeeName
ORDER BY COUNT(EmployeeID) DESC;
Example
![SelectHavingClouse]()
Summary
In this tutorial, we learned the basics of the SQL SELECT GROUP BY statement.