Aggregate Functions - COUNT, SUM, AVG, MIN, MAX in SQL

In SQL, aggregate functions are used to perform calculations on multiple rows of data to return a single result. These functions are typically used in conjunction with the GROUP BY clause to group the rows before performing the calculation. The most commonly used aggregate functions in SQL include COUNT, SUM, AVG, MIN, and MAX.

1. COUNT - Counting Rows

The COUNT function is used to count the number of rows that match a specified condition. It is often used to determine the number of records in a table or the number of records that satisfy a specific condition.

Syntax:

      SELECT COUNT(column_name)
      FROM table_name
      WHERE condition;
    

COUNT can be used on a column or a wildcard (*) to count rows in a table.

Example: Using COUNT

      -- Count the number of employees
      SELECT COUNT(EmployeeID)
      FROM Employees;
    

This query counts the total number of employees in the Employees table.

Example: COUNT with Condition

      -- Count the number of employees with a salary greater than 50000
      SELECT COUNT(EmployeeID)
      FROM Employees
      WHERE Salary > 50000;
    

This query counts the number of employees whose salary is greater than 50,000.

2. SUM - Summing Values

The SUM function is used to calculate the total sum of a numeric column. It is often used to sum up values such as sales, salary, or quantity.

Syntax:

      SELECT SUM(column_name)
      FROM table_name
      WHERE condition;
    

SUM can only be used on numeric columns, and it returns the sum of the values in that column.

Example: Using SUM

      -- Calculate the total salary of all employees
      SELECT SUM(Salary)
      FROM Employees;
    

This query calculates the total salary of all employees in the Employees table.

Example: SUM with Condition

      -- Calculate the total salary of employees with a salary greater than 50000
      SELECT SUM(Salary)
      FROM Employees
      WHERE Salary > 50000;
    

This query calculates the total salary of employees whose salary is greater than 50,000.

3. AVG - Averaging Values

The AVG function is used to calculate the average value of a numeric column. It is useful when you need to find the mean value of a set of numbers.

Syntax:

      SELECT AVG(column_name)
      FROM table_name
      WHERE condition;
    

AVG can only be used on numeric columns, and it returns the average of the values in that column.

Example: Using AVG

      -- Calculate the average salary of all employees
      SELECT AVG(Salary)
      FROM Employees;
    

This query calculates the average salary of all employees in the Employees table.

Example: AVG with Condition

      -- Calculate the average salary of employees who have been with the company for more than 5 years
      SELECT AVG(Salary)
      FROM Employees
      WHERE YearsAtCompany > 5;
    

This query calculates the average salary of employees who have been with the company for more than 5 years.

4. MIN - Minimum Value

The MIN function is used to find the minimum value in a column. It is commonly used for finding the smallest value in a set of data, such as the lowest salary or the earliest date.

Syntax:

      SELECT MIN(column_name)
      FROM table_name
      WHERE condition;
    

MIN can be used on any data type, including numeric, date, and text columns, and it returns the smallest value.

Example: Using MIN

      -- Find the lowest salary among all employees
      SELECT MIN(Salary)
      FROM Employees;
    

This query retrieves the lowest salary from the Employees table.

Example: MIN with Condition

      -- Find the earliest hire date of employees who have been with the company more than 10 years
      SELECT MIN(HireDate)
      FROM Employees
      WHERE YearsAtCompany > 10;
    

This query finds the earliest hire date of employees who have been with the company for more than 10 years.

5. MAX - Maximum Value

The MAX function is used to find the maximum value in a column. It is commonly used for finding the highest value, such as the highest salary or the latest date.

Syntax:

      SELECT MAX(column_name)
      FROM table_name
      WHERE condition;
    

MAX can be used on any data type, including numeric, date, and text columns, and it returns the largest value.

Example: Using MAX

      -- Find the highest salary among all employees
      SELECT MAX(Salary)
      FROM Employees;
    

This query retrieves the highest salary from the Employees table.

Example: MAX with Condition

      -- Find the most recent hire date of employees who have been with the company more than 5 years
      SELECT MAX(HireDate)
      FROM Employees
      WHERE YearsAtCompany > 5;
    

This query finds the most recent hire date of employees who have been with the company for more than 5 years.

6. Combining Aggregate Functions

SQL allows you to combine multiple aggregate functions in a single query. This can be useful when you want to perform different calculations on the same set of data.

Example: Combining COUNT, SUM, and AVG

      -- Calculate the number of employees, total salary, and average salary
      SELECT COUNT(EmployeeID), SUM(Salary), AVG(Salary)
      FROM Employees;
    

This query calculates the total number of employees, the total salary, and the average salary in the Employees table in a single query.

7. Conclusion

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX are essential tools in SQL for performing calculations on large sets of data. These functions allow you to easily summarize and analyze data in a table, and they can be used in combination with the GROUP BY clause to group the data before performing the aggregation. Understanding how to use these functions will help you write more efficient and powerful SQL queries.