AVG(), SUM(), COUNT(), MIN(), MAX()

The aggregate functions `AVG()`, `SUM()`, `COUNT()`, `MIN()`, and `MAX()` in SQL are used for analyzing and summarizing data in a table. Here are the key differences between these aggregate functions:

1. AVG() - Average

   - Purpose: Calculates the average value of a numeric column.

   - Syntax: AVG(column_name)

   - Example:

     SELECT AVG(salary) AS average_salary
     FROM employees;

   - Result: Returns a single value representing the average of the values in the specified column.

2. SUM() - Summation

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

   - Syntax: SUM(column_name)

   - Example:

     SELECT SUM(revenue) AS total_revenue
     FROM sales;

   - Result: Returns a single value representing the sum of the values in the specified column.

3. COUNT() - Counting

   - Purpose: Counts the number of rows in a table or the number of non-NULL values in a column.

   - Syntax: COUNT(column_name)

   - Example:

     SELECT COUNT(employee_id) AS total_employees
     FROM employees;

   - Result: Returns a single value representing the count of rows or non-NULL values.

4. MIN() - Minimum

   - Purpose: Finds the minimum (smallest) value in a column.

   - Syntax: MIN(column_name)

   - Example:

     SELECT MIN(order_date) AS earliest_order_date
     FROM orders;

  - Result: Returns a single value representing the minimum value in the specified column.

5. MAX() - Maximum

   - Purpose: Finds the maximum (largest) value in a column.

   - Syntax: MAX(column_name)

   - Example:

     SELECT MAX(salary) AS highest_salary
     FROM employees;

   - Result: Returns a single value representing the maximum value in the specified column.

Key Differences

Calculation

  - `AVG()` calculates the average.

  - `SUM()` calculates the total sum.

  - `COUNT()` counts rows or non-NULL values.

  - `MIN()` finds the minimum.

  - `MAX()` finds the maximum.

Result Type

  - `AVG()`, `SUM()`, `MIN()`, and `MAX()` return a single numeric value.

  - `COUNT()` returns a count, which is an integer.

Null Values

  - `AVG()`, `SUM()`, `MIN()`, and `MAX()` generally ignore NULL values.

  - `COUNT()` counts all rows, including those with NULL values (unless specified otherwise).

Applicability

  - `AVG()` is used for central tendency analysis.

  - `SUM()` is used for total accumulation.

  - `COUNT()` is used for counting rows.

  - `MIN()` and `MAX()` are used for finding extremes.

These aggregate functions are essential for summarizing and gaining insights into data within a database. They are often used in combination with the `GROUP BY` clause to perform analysis on subsets of data.