GROUP BY Clause

The GROUP BY clause in SQL Server is used to group rows that have the same values in specified columns into aggregate data. This clause is often used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to perform operations on each group of data.

Basic Syntax

SELECT 

   column1,

   column2,

   aggregate_function(column3)

FROM 

   table_name

GROUP BY 

   column1,

   column2;

Examples and Scenarios

• Scenario 1: Grouping and Counting Rows

Example: Counting Employees in Each Department

SELECT 

   DepartmentID,

   COUNT(EmployeeID) AS EmployeeCount

FROM 

   Employees

GROUP BY 

   DepartmentID;

• This query counts the number of employees in each department.

Ready to Code Like a Pro? Enroll in Our SQL Server Developer Course at TrainingHub.io!

• Scenario 2: Grouping and Summing

Example: Total Sales per Customer

SELECT

CustomerID,

SUM(TotalAmount) AS TotalSales

FROM

Sales

GROUP BY

CustomerID;

• This query calculates the total sales for each customer.

• Scenario 3: Grouping by Multiple Columns

Example: Total Sales per Customer per Year

SELECT 

    CustomerID,

   YEAR(SaleDate) AS SaleYear,

   SUM(TotalAmount) AS TotalSales

FROM 

   Sales

GROUP BY 

   CustomerID,

   YEAR(SaleDate);

• This query calculates the total sales for each customer for each year.

Scenario 4: Using HAVING Clause with GROUP BY

The HAVING clause is used to filter groups based on aggregate functions, similar to the WHERE clause but for groups.

Example: Customers with Total Sales Greater than 1000

SELECT 

   CustomerID,

   SUM(TotalAmount) AS TotalSales

FROM 

   Sales

GROUP BY 

   CustomerID

HAVING 

   SUM(TotalAmount) > 1000;

• This query selects customers whose total sales exceed 1000.

• Scenario 5: Average Calculation

Example: Average Salary by Department

SELECT 

   DepartmentID,

   AVG(Salary) AS AverageSalary

FROM 

   Employees

GROUP BY 

   DepartmentID;

• This query calculates the average salary for each department.

Our Data Analyst courses in Toronto at TrainingHub.io are crafted to help you excel in data interpretation and presentation.

• Scenario 6: Finding Maximum and Minimum Values

Example: Maximum and Minimum Order Amount per Customer

SELECT 

   CustomerID,

   MAX(OrderAmount) AS MaxOrderAmount,

   MIN(OrderAmount) AS MinOrderAmount

FROM 

   Orders

GROUP BY 

   CustomerID;

• This query finds the maximum and minimum order amounts for each customer.

• Scenario 7: Grouping and Joining Tables

Example: Total Sales per Customer with Customer Details

SELECT

C.CustomerID,

C.CustomerName,

SUM(S.TotalAmount) AS TotalSales

FROM

Customers C

JOIN

Sales S ON C.CustomerID = S.CustomerID

GROUP BY

C.CustomerID,

C.CustomerName;

• This query joins the Customers and Sales tables and then groups the results to calculate the total sales for each customer.

• Scenario 8: Nested Grouping

Example: Average Order Amount per Customer per Year

SELECT

CustomerID,

YEAR(OrderDate) AS OrderYear,

AVG(OrderAmount) AS AverageOrderAmount

FROM

Orders

GROUP BY

CustomerID,

YEAR(OrderDate);

• This query calculates the average order amount for each customer for each year.

• Scenario 9: Grouping by Expressions

Example: Sales by Quarter

SELECT

YEAR(SaleDate) AS SaleYear,

DATEPART(QUARTER, SaleDate) AS SaleQuarter,

SUM(TotalAmount) AS TotalSales

FROM

Sales

GROUP BY

YEAR(SaleDate),

DATEPART(QUARTER, SaleDate);

• This query calculates the total sales for each quarter of each year.

Important Notes

1. Columns in SELECT Clause: Any column in the SELECT clause that is not used in an aggregate function must be included in the GROUP BY clause.

2. HAVING vs. WHERE: The WHERE clause filters rows before grouping, whereas the HAVING clause filters groups after aggregation.

3. NULL Values: NULL values are considered equal when grouping.

TrainingHub.io, known as the best IT training institute in Canada, offers a diverse range of courses to cater to all learning needs.