JOIN WITH WHERE, GROUP BY, ORDER BY, and HAVING Clauses

Definition:  

The GROUP BY clause groups together rows with similar values into summary rows, which allows for tasks such as counting the number of customers in each country. This clause is frequently used in combination with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result set based on one or more columns. 

Syntax: 
SELECT Column_Name 
FROM Table_Name 
GROUP BY Column_Name 

Tables used in below examples  

1.Table1 

CREATE TABLE Customers ( 
                          ID INT PRIMARY KEY, 
                         Name VARCHAR(50) NOT NULL, 
                         Email VARCHAR(50) NOT NULL  
                       ); 

INSERT INTO Customers(
ID,
Name,
Email) 
VALUES  
  (1, ‘Alice’, ‘alice@gmail.com’), 
  (2, ‘Bob’, ‘bob@gmail.com’), 
  (3, ‘Charlie’, ‘charlie@gmail.com’); 

Overview of table

SELECT *
FROM Customers

2.Table2 

CREATE TABLE OrderDetails ( 
                             ID INT PRIMARY KEY, 
                             Customer_id INT, 
                            Product VARCHAR(50),  
                            Quantity INT 
                          ); 

INSERT INTO  OrderDetails(
ID,
Customer_id,
Product, Quantity
) 
VALUES  
(1, 1, ‘Apple’, 2), 
  (2, 1, ‘Banana’, 3), 
  (3, 2, ‘Orange’, 1), 
  (4, 2, ‘Apple’, 5), 
  (5, 3, ‘Banana’, 2), 
  (6, 3, ‘Apple’, 1); 

Overview of table

SELECT *
FROM OrderDetails

Examples:  

Level1:- Join With Group By 

SELECT  
       Customers.Name,  
       SUM(OrderDetails.quantity) AS Total_Quantity 
FROM  
       dbo.Customers 
JOIN 
       dbo.OrderDetails 
ON  
       Customers.ID = OrderDetails.Customer_Id 
GROUP BY  
       Customers.Name; 

Result : 

Level2:- Join With Group By & OrderBy 

SELECT  
       Customers.Name,  
       SUM(OrderDetails.quantity) AS total_Quantity 
FROM  
       dbo.Customers 
JOIN  
       dbo.OrderDetails 
ON  
       Customers.ID = OrderDetails.customer_Id 
GROUP BY 
       Customers.Name 
ORDER BY  
       Total_Quantity asc; 

Result : 

Level3:- Join With Where clause, Group By & OrderBy 

SELECT  
      Customers.Name, 
      SUM(OrderDetails.quantity) AS Total_Quantity,          
      OrderDetails.Product 
FROM  
      dbo.Customers 
JOIN  
      dbo.OrderDetails 
ON  
      Customers.ID = OrderDetails.customer_id 
WHERE Product = ‘Apple’  
GROUP BY Customers.name, OrderDetails.Product 
ORDER BY Customers.Name asc; 

Result : 

Level4:- Join With Where clause, Group By, Having  & OrderBy 

SELECT  
      Customers.Name,  
      SUM(OrderDetails.quantity)AS Total_Quantity, 
      OrderDetails.Product 
FROM  
      dbo.Customers 
JOIN  
      dbo.OrderDetails 
ON Customers.ID = OrderDetails.customer_id 
WHERE Product = ‘Apple’  
GROUP BY Customers.name, OrderDetails.Product 
HAVING SUM(OrderDetails.quantity) > 1 
ORDER BY Customers.Name asc; 

Result :