In this Post we will learn about  Aggregate functions  and will see how we can answer below questions

1. Total Salary by Department

2. Number of Employees in Each Department:

3. Average Salary in Each Department

4. Maximum and Minimum Salary in Each Department:

5. Total Sales Amount, Average Sale Amount, and Number of Sales per Employee:

6. Total Sales Amount per Year:

7. Highest and Lowest Sale Amount per Year

All these queries required to use below Aggregate functions.

• SUM() - returns the total sum of a numerical column
• COUNT() - returns the number of rows in a set
• AVG() - returns the average value of a numerical column
• MIN() - returns the smallest value within the selected column
• MAX() - returns the largest value within the selected column

Let's Start with Table Creation

a. Create Below Tables Employees, Departments ,Sales

📄
-- Step 1: Create the necessary tables

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2),
    HireDate DATE
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    EmployeeID INT,
    SaleDate DATE,
    SaleAmount DECIMAL(10, 2),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

Insert Data into tables

📄
-- Step 2: Insert sample data into the tables

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Medical'),
(4, 'Tranport');

INSERT INTO Employees (EmployeeID, Name, DepartmentID, Salary, HireDate) VALUES
(1, 'Devesh omar', 1, 510000.00, '2021-01-15'),
(2, 'Himanshu', 2, 640000.00, '2011-03-22'),
(3, 'Nikhil', 3, 55000.00, '2021-05-10'),
(4, 'Deepu', 2, 740000.00, '2011-07-19'),
(5, 'Hari', 3, 85000.00, '2019-09-25'),
(6, 'Jai', 4, 815000.00, '2014-09-15');

INSERT INTO Sales (SaleID, EmployeeID, SaleDate, SaleAmount) VALUES
(1, 3, '2023-01-15', 1000.00),
(2, 3, '2023-03-22', 800.00),
(3, 3, '2023-05-10', 1500.00),
(4, 5, '2023-07-19', 3000.00),
(5, 5, '2023-09-25', 2000.00),
(6, 6, '2023-11-30', 2500.00),
(7, 3, '2024-01-10', 1100.00),
(8, 3, '2024-02-15', 850.00),
(9, 5, '2024-04-20', 1600.00),
(10, 5, '2024-06-25', 3200.00);

Emp Table

Department Table

Sales Table

Query 1 : Calculate total salary provided to employees by department

• This query used for business purpose when company want to know cost or salary distribution by each department
• This query calculates the total salary for each department.
• It uses the SUM aggregate function.

📄
SELECT 
    d.DepartmentName,
    SUM(e.Salary) AS TotalSalary
FROM 
    Employees e
JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY 
    d.DepartmentName
ORDER BY 
    d.DepartmentName;

Result

Query 2: Count the number of employees in each department

• This query counts the number of employees in each department.
• It uses the COUNT aggregate function.

📄
-- Query 2: Count the number of employees in each department
SELECT 
    d.DepartmentName,
    COUNT(e.EmployeeID) AS NumberOfEmployees
FROM 
    Employees e
JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY 
    d.DepartmentName
ORDER BY 
    d.DepartmentName;

Result

Query 3: Calculate the average salary in each department

• This query used for business purpose when company want to know Average cost or salary distribution by each department
• This query calculates the average salary for each department.
• It uses the AVG aggregate function.

Result

Query 4: Find the maximum and minimum salary in each department

• This query used for business purpose when company want to know Max /Min Salary provided to employees by each department
• It uses the MAX and MIN aggregate functions.

📄
SELECT 
    d.DepartmentName,
    MAX(e.Salary) AS MaxSalary,
    MIN(e.Salary) AS MinSalary
FROM 
    Employees e
JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY 
    d.DepartmentName
ORDER BY 
    d.DepartmentName;Result

Result

Query 5: Calculate total sales amount, average sale amount, and the number of sales done by per employee

• This query calculates the total sales amount, average sale amount, and the number of sales for each employee.
• It uses the SUM, AVG, and COUNT aggregate functions.

📄
SELECT 
    e.Name AS EmployeeName,
    SUM(s.SaleAmount) AS TotalSales,
    AVG(s.SaleAmount) AS AverageSaleAmount,
    COUNT(s.SaleID) AS NumberOfSales
FROM 
    Sales s
JOIN 
    Employees e ON s.EmployeeID = e.EmployeeID
GROUP BY 
    e.Name
ORDER BY 
    e.Name;

Result

Query 6: Calculate total sales amount per year

• This query calculates the total sales amount for each year.
• It uses the SUM aggregate function.

📄
SELECT 
    YEAR(SaleDate) AS SaleYear,
    SUM(SaleAmount) AS TotalSales
FROM 
    Sales
GROUP BY 
    YEAR(SaleDate)
ORDER BY 
    SaleYear;

Query 7: Calculate the highest and lowest sale amount per year

• This query finds the highest and lowest sale amounts for each year.
• It uses the MAX and MIN aggregate functions.

📄
SELECT 
    YEAR(SaleDate) AS SaleYear,
    MAX(SaleAmount) AS MaxSaleAmount,
    MIN(SaleAmount) AS MinSaleAmount
FROM 
    Sales
GROUP BY 
    YEAR(SaleDate)
ORDER BY 
    SaleYear;

Result

Thanks

Happy Coding and Happy learning