SQL GROUP BY Clause

SELECT Department,
       COUNT(*) AS Total_Students
FROM Students
GROUP BY Department;

SELECT Department,
       Gender,
       COUNT(*) AS Total
FROM Employees
GROUP BY Department, Gender;

SELECT Gender, COUNT(*) AS Total
FROM Employees
WHERE Department = 'IT'
GROUP BY Gender;

SELECT Category, AVG(Price) AS Avg_Price
FROM Products
WHERE Price > 500
GROUP BY Category;

SELECT Department, AVG(CGPA) AS Avg_CGPA
FROM Students
WHERE Department = 'CSE'
GROUP BY Department;

SELECT Department, COUNT(*) AS Total_Students
FROM Students
GROUP BY Department
HAVING COUNT(*) > 100;

SELECT Category, AVG(Price) AS Avg_Price
FROM Products
GROUP BY Category
HAVING AVG(Price) > 500;

SELECT City, SUM(Amount) AS Total_Sales
FROM Orders
GROUP BY City
HAVING SUM(Amount) > 100000;

SELECT Category, COUNT(*) AS Total_Products FROM Products GROUP BY Category;

SELECT Category, SUM(Units_Sold * Price) AS Total_Sales FROM Products GROUP BY Category;

SELECT Brand, AVG(Price) AS Avg_Price FROM Products GROUP BY Brand;

SELECT MAX(Price) AS Highest_Price FROM Products;

SELECT MIN(Price) AS Lowest_Price FROM Products;

SELECT Category, COUNT(*) AS Total
FROM Products
GROUP BY Category
HAVING COUNT(*) > 20;

SELECT MONTH(Order_Date) AS Month, SUM(Amount) AS Total_Sales
FROM Orders
GROUP BY MONTH(Order_Date);

CREATE DATABASE sales_demo;
USE sales_demo;

CREATE TABLE Products (
    Product_ID   INT PRIMARY KEY,
    Product_Name VARCHAR(100),
    Category     VARCHAR(50),
    Brand        VARCHAR(50),
    Price        DECIMAL(10,2),
    Units_Sold   INT
);

INSERT INTO Products VALUES
(1, 'Mouse', 'Electronics', 'Logitech', 450.00, 120),
(2, 'Keyboard', 'Electronics', 'HP', 1200.00, 80),
(3, 'Notebook', 'Stationery', 'Generic', 60.00, 300),
(4, 'Pen', 'Stationery', 'Generic', 20.00, 500),
(5, 'Monitor', 'Electronics', 'Samsung', 9500.00, 30);

SELECT COUNT(*) AS Total FROM Products;

SELECT SUM(Units_Sold) AS Total_Units FROM Products;

SELECT AVG(Price) AS Avg_Price FROM Products;

SELECT MAX(Price) AS Highest, MIN(Price) AS Lowest FROM Products;

SELECT Category, COUNT(*) AS Total, AVG(Price) AS Avg_Price
FROM Products
GROUP BY Category;

SELECT Category, AVG(Price) AS Avg_Price
FROM Products
WHERE Price > 100
GROUP BY Category;

SELECT Category, COUNT(*) AS Total
FROM Products
GROUP BY Category
HAVING COUNT(*) >= 3;

SELECT Category,
       COUNT(*) AS Total_Products,
       SUM(Units_Sold) AS Total_Units,
       AVG(Price) AS Avg_Price,
       MAX(Price) AS Max_Price,
       MIN(Price) AS Min_Price
FROM Products
GROUP BY Category
HAVING COUNT(*) >= 2
ORDER BY Total_Units DESC;

-- ১. Sales by Category
SELECT Category, SUM(Amount) AS Total_Sales FROM Sales GROUP BY Category;

-- ২. Revenue by Brand
SELECT Brand, SUM(Amount) AS Revenue FROM Sales GROUP BY Brand;

-- ৩. Orders by City
SELECT City, COUNT(*) AS Total_Orders FROM Sales GROUP BY City;

-- ৪. Monthly Sales Report
SELECT MONTH(Sale_Date) AS Month, SUM(Amount) AS Total FROM Sales GROUP BY MONTH(Sale_Date);

-- ৫. Average Product Price
SELECT Category, AVG(Price) AS Avg_Price FROM Products GROUP BY Category;

-- ৬. Highest Revenue Category
SELECT Category, SUM(Amount) AS Revenue
FROM Sales GROUP BY Category ORDER BY Revenue DESC LIMIT 1;

-- ৭. Department-wise Employee Summary
SELECT Department, COUNT(*) AS Total_Employees, AVG(Salary) AS Avg_Salary
FROM Employees GROUP BY Department;