3.aggregate_functions.md

SQL Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. These functions are essential for data analysis and reporting in SQL.

Common Aggregate Functions

1. COUNT()

• Purpose: Counts the number of rows or non-null values in a column.
• Syntax:

  -- Count all rows
  SELECT COUNT(*) FROM table_name;
  
  -- Count non-null values in a column
  SELECT COUNT(column_name) FROM table_name;

• Example:

  -- Count total number of employees
  SELECT COUNT(*) as TotalEmployees
  FROM Employees;
  
  -- Count employees in each department
  SELECT Department, COUNT(*) as DepartmentCount
  FROM Employees
  GROUP BY Department;

2. SUM()

• Purpose: Calculates the total sum of numeric values in a column.
• Syntax:

  SELECT SUM(column_name) FROM table_name;

• Example:

  -- Calculate total salary budget
  SELECT SUM(Salary) as TotalSalary
  FROM Employees;
  
  -- Calculate total salary by department
  SELECT Department, SUM(Salary) as DepartmentBudget
  FROM Employees
  GROUP BY Department;

3. AVG()

• Purpose: Calculates the average (mean) of numeric values in a column.
• Syntax:

  SELECT AVG(column_name) FROM table_name;

• Example:

  -- Calculate average salary across company
  SELECT ROUND(AVG(Salary), 2) as AverageSalary
  FROM Employees;
  
  -- Calculate average salary by department
  SELECT Department,
         ROUND(AVG(Salary), 2) as AvgDepartmentSalary
  FROM Employees
  GROUP BY Department;

4. MAX()

• Purpose: Finds the maximum value in a column.
• Syntax:

  SELECT MAX(column_name) FROM table_name;

• Example:

  -- Find highest salary in company
  SELECT MAX(Salary) as HighestSalary
  FROM Employees;
  
  -- Find highest salary in each department
  SELECT Department, MAX(Salary) as HighestSalary
  FROM Employees
  GROUP BY Department;

5. MIN()

• Purpose: Finds the minimum value in a column.
• Syntax:

  SELECT MIN(column_name) FROM table_name;

• Example:

  -- Find lowest salary in company
  SELECT MIN(Salary) as LowestSalary
  FROM Employees;
  
  -- Find lowest salary in each department
  SELECT Department, MIN(Salary) as LowestSalary
  FROM Employees
  GROUP BY Department;

Combining Aggregate Functions

• Purpose: Multiple aggregate functions can be used together for comprehensive analysis.
• Example:

  -- Comprehensive salary analysis by department
  SELECT 
      Department,
      COUNT(*) as EmployeeCount,
      ROUND(AVG(Salary), 2) as AverageSalary,
      SUM(Salary) as TotalSalary,
      MAX(Salary) as HighestSalary,
      MIN(Salary) as LowestSalary
  FROM Employees
  GROUP BY Department;

Practical Session 🔴

-- Let's analyze our employee data

-- Basic counts
SELECT COUNT(*) as TotalEmployees,
       COUNT(DISTINCT Department) as NumberOfDepartments
FROM Employees;

-- Salary statistics by department
SELECT 
    Department,
    COUNT(*) as EmployeeCount,
    ROUND(AVG(Salary), 2) as AverageSalary,
    SUM(Salary) as DepartmentBudget,
    MAX(Salary) as HighestSalary,
    MIN(Salary) as LowestSalary
FROM Employees
GROUP BY Department
ORDER BY DepartmentBudget DESC;

-- Find departments with average salary above company average
SELECT Department,
       ROUND(AVG(Salary), 2) as DeptAvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > (SELECT AVG(Salary) FROM Employees);

Aggregate functions are powerful tools for data analysis in SQL. They allow you to:

• Calculate totals with SUM
• Find averages with AVG
• Get counts with COUNT
• Find maximum values with MAX
• Find minimum values with MIN

Combining these functions with GROUP BY and HAVING clauses enables complex data analysis and reporting capabilities.