[Basic Grammar] SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values and it is usually used in aggregate functions ( COUNT (), MAX(), MIN(), SUM(), AVG()).

 

• GROUP BY Syntax 

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example 

Write an SQL query to find the total number of orders placed by each customer in the "Orders" table. Group the results by customer, and display the customer's name along with the count of their orders.

SELECT CustomerID, COUNT (OrderID) AS OrderCount 
FROM Orders
GROUP BY CustomerID;

 

• GROUP BY with JOIN 

Example 

Write an SQL query to find the total sales amount for each product category in the "Products" table. Join the "Products" and "Categories" tables to retrieve the product category names and their corresponding sales amounts. Group the results by the category name and calculate the total sales amount within each category.

SELECT Categories.CategoryName, SUM(Products.SalesAmount) AS TotalSales
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
GROUP BY Categories.CategoryName;

 

Exercises 

Write an SQL query to find the average salary for each department in a company. Join the "Employees" and "Departments" tables to retrieve employee salaries and their respective departments. Group the results by department, and calculate the average salary for each department.

SELECT Departments.DepartmentName, AVG(Employees.Salary) AS AverageSalary
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY Departments.DepartmentName;