SQL SELECT - GROUP BY Statement

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
  1. SELECT column_name(s)      
  2. FROM table_name      
  3. WHERE condition      
  4. 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  
  1. Create table CarSale        
  2. (        
  3. Id int primary key identity(1,1) ,         
  4. Model varchar(200),        
  5. Price money        
  6. )        
  7.         
  8. Insert into CarSale values('Baleno', 900000),('M551', 950000),('M551', 950000),('L551', 950000),        
  9. ('L538', 650000),('L538', 900000),('BWW', 8000000),('8530', 450000),('L530',380000), ('L545', 280000)        
Example  
 
 
 

SQL Aggregate functions 

 

MAX() 

 
This function returns the maximum value of the numeric column of specified criteria
 
Syntax
  1. Select max(Price) As 'MaximumCostOfModel' from CarSale      
Example  
 

MIN() 

 
This function returns the minimum of the numeric column of specified criteria
  
Syntax
  1. Select Min(Price) As 'MinimumCostOfModel' from  CarSale    
Example
 
 

SUM() 

  
This function returns the total sum of a numeric column of specified criteria
 
Syntax      
  1. Select SUM(Price) As 'SumCostOfAllModel' from CarSale   
Example 
 
  

AVG() 

 
the function returns the average value of a numeric column of specified criteria
 
Syntax 
  1. Select AVG(Price) As 'AverageCostOfModel' from  CarSale         
Example 
 

COUNT() 

 
This function returns the number of rows that match specified criteria
  
Syntax
  1. Select Count(Price) As 'TotalVehicleModels' from CarSale       
Example
 
  

Distinct clause 

 
The distinct clause is used to filter unique records out of the duplicate records that satisfy the query criteria
  
Syntax     
  1. Select Distinct(Model),  Price from CarSale  
Example 
 
 

SELECT a simple GROUP BY clause

 
The SELECT GROUP BY statement lists the Employee of EmployeeDetails
 
Syntax   
  1. SELECT EmployeeName,EmployeeAddress as EmployeeCity        
  2. FROM EmployeeDetails        
  3. GROUP BY EmployeeName, EmployeeAddress ORDER BY EmployeeName;  
Example 
SQL SELECT - GROUP BY Statement
 
The SQL GROUP BY statement list the number of Employee in each EmployeeId, sorted high to low
 
Syntax  
  1. SELECT COUNT(EmployeeId), EmployeeName      
  2. FROM EmployeeDetails      
  3. GROUP BY EmployeeName      
  4. ORDER BY COUNT(EmployeeID) DESC;    
Example 
 
SQL SELECT - GROUP BY Statement
 

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 sample
 
Syntax
  1. SELECT EmployeeName      
  2. FROM EmployeeDetails      
  3. INNER JOIN  EmployeeDetail      
  4. ON EmployeeDetails.EmployeeID=EmployeeDetail.EmpId GROUP BY EmployeeName ORDER BY EmployeeName;     
Example
 
SQL SELECT - GROUP BY Statement
 

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
  1. SELECT COUNT(EmployeeID), EmployeeName      
  2. FROM EmployeeDetails      
  3. GROUP BY EmployeeName;    
Example
 
SQL SELECT - GROUP BY Statement
 

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
  1. SELECT COUNT(EmployeeID)EmployeeAdress      
  2. FROM EmployeeDetails      
  3. GROUP BY EmployeeName       
  4. HAVING COUNT(EmployeeID)> 1;    
Example 
 
SQL SELECT - GROUP BY Statement
 

SELECT GROUP BY basic use statement

 
The SELECT GROUP BY clause finds the total EmployeeID, EmployeeName for EmployeeDetails.
 
Syntax
  1. SELECT COUNT(EmployeeID), EmployeeName from EmployeeDetails GROUP BY EmployeeName       
Example  
 
SQL SELECT - GROUP BY Statement
 

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 
  1. SELECT COUNT(EmployeeID), EmployeeName , EmployeeAddress        
  2. FROM EmployeeDetails        
  3. GROUP BY EmployeeName WITH (DISTRIBUTED_AGG), EmployeeAddress         
  4. ORDER BY EmployeeName DESC       
Example   
 
SQL SELECT - GROUP BY Statement
 

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 
  1. SELECT EmployeeName, EmployeeAddress FROM EmployeeDetails GROUP BY EmployeeName, EmployeeAddress;          
  2. SELECT EmpName FROM  EmployeeDetail GROUP BY EmpName, EmpAddress;     
Example
 
SQL SELECT - GROUP BY Statement
 

SELECT GROUP BY with multiple GROUP BY expressions 

 
The following example groups result using multiple GROUP BY EmployeeName If, within each EmployeeId group
 
Syntax
  1. SELECT COUNT(EmployeeId), EmployeeName      
  2. FROM EmployeeDetails      
  3. GROUP BY EmployeeName      
  4. ORDER BY COUNT(EmployeeID) DESC;   
Example 
 
SQL SELECT - GROUP BY Statement
 

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 
  1. SELECT COUNT(EmployeeId), EmployeeName        
  2. FROM EmployeeDetails        
  3. GROUP BY EmployeeName        
  4. ORDER BY COUNT(EmployeeID) DESC;     
Example
 
SQL SELECT - GROUP BY Statement
 

Summary

 
In this tutorial, we learned the basics of the SQL SELECT GROUP BY statement.