Introduction
In this chapter, we will learn about SELECT- GROUP BY SQL Statement and its uses into the SQL.
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, GROUP BY statement is placed after the WHERE clause
- In the query, GROUP BY clause is placed before 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
MAX()
This function returns the maximum value of the numeric column of specified criteria
Syntax
- Select max(Price) As 'MaximumCostOfModel' from CarSale
Example
This function returns the minimum of the numeric column of specified criteria
Syntax
- Select Min(Price) As 'MinimumCostOfModel' from CarSale
Example
SUM()
This function returns the total sum of a numeric column of specified criteria
Syntax
- Select SUM(Price) As 'SumCostOfAllModel' from CarSale
Example
AVG()
the function returns the average value of a numeric column of specified criteria
Syntax
- Select AVG(Price) As 'AverageCostOfModel' from CarSale
Example
This function returns the number of rows that match specified criteria
Syntax
- Select Count(Price) As 'TotalVehicleModels' from CarSale
Example
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
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
The SQL GROUP BY statement list the number of Employee in each EmployeeId, sorted high to low
Syntax
- SELECT COUNT(EmployeeId), EmployeeName
- FROM EmployeeDetails
- GROUP BY EmployeeName
- ORDER BY COUNT(EmployeeID) DESC;
Example
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
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 GROUP BY clause statement.
Syntax
- SELECT COUNT(EmployeeID), EmployeeName
- FROM EmployeeDetails
- GROUP BY EmployeeName;
Example
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
SELECT GROUP BY basic use
The SELECT GROUP BY clause finds the total EmployeeID, EmployeeName for EmployeeDetails.
Syntax
- SELECT COUNT(EmployeeID), EmployeeName from EmployeeDetails GROUP BY EmployeeName
Example
Basic use of the DISTRIBUTED_AGG hint
The SELECT the DISTRIBUTED_AGG query hint to force 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
SELECT Variations for GROUP BY statement
The SELECT variations for GROUP BY statement is the select list has no aggregations, 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
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 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
Summary
In the next chapter, we will learn the basics of the SQL SELECT HAVING statement.