4.1 Aggregate Functions: SUM, AVG, COUNT, MAX, MIN

Aggregate functions in SQL allow you to perform calculations on multiple rows of data and return a single result. These functions are useful for summarizing data, calculating totals, averages, counts, and finding the highest or lowest values in a dataset.

4.1.1 Overview of Aggregate Functions

Aggregate functions operate on a set of rows and return a single value. They are often used with the GROUP BY clause to group data into subsets and perform calculations on each group. These functions are fundamental for generating reports and analyzing data.

4.1.2 Common Aggregate Functions

1. SUM()

The SUM() function calculates the total sum of a numeric column. It is commonly used to calculate total sales, total salary, or any other summation of numeric data.

• Purpose: To calculate the sum of values in a numeric column.
• Syntax:

  SELECT SUM(column_name)
  FROM table_name;
  

• Example:
  Calculate the total salary of employees:

  SELECT SUM(salary) AS total_salary
  FROM employees;
  

2. AVG()

The AVG() function calculates the average (mean) of a numeric column. It is used to find average values, such as average salary, average price, or average score.

• Purpose: To calculate the average of values in a numeric column.
• Syntax:

  SELECT AVG(column_name)
  FROM table_name;
  

• Example:
  Calculate the average salary of employees:

  SELECT AVG(salary) AS average_salary
  FROM employees;
  

3. COUNT()

The COUNT() function counts the number of rows in a table or the number of non-NULL values in a column. It is commonly used to find the number of records, such as the number of customers, orders, or employees.

• Purpose: To count the number of rows or non-NULL values in a column.
• Syntax:

  SELECT COUNT(column_name)
  FROM table_name;
  

• Example:
  Count the total number of employees in the "employees" table:

  SELECT COUNT(*) AS total_employees
  FROM employees;
  

4. MAX()

The MAX() function returns the maximum value from a numeric column. It can be used to find the highest salary, the highest score, or the maximum price.

• Purpose: To find the maximum value in a column.
• Syntax:

  SELECT MAX(column_name)
  FROM table_name;
  

• Example:
  Find the highest salary in the company:

  SELECT MAX(salary) AS highest_salary
  FROM employees;
  

5. MIN()

The MIN() function returns the minimum value from a numeric column. It is used to find the lowest value, such as the lowest price, lowest salary, or minimum score.

• Purpose: To find the minimum value in a column.
• Syntax:

  SELECT MIN(column_name)
  FROM table_name;
  

• Example:
  Find the lowest salary in the company:

  SELECT MIN(salary) AS lowest_salary
  FROM employees;
  

4.1.3 Using Aggregate Functions with GROUP BY

You can use aggregate functions in combination with the GROUP BY clause to calculate values for different groups within a dataset. The GROUP BY clause groups rows that have the same values in specified columns into summary rows, such as calculating the total salary for each department or counting the number of orders per customer.

• Syntax:

  SELECT column_name, AGGREGATE_FUNCTION(column_name)
  FROM table_name
  GROUP BY column_name;
  

• Example:
  Calculate the total salary per department:

  SELECT department, SUM(salary) AS total_salary
  FROM employees
  GROUP BY department;
  

• Example:
  Count the number of orders placed by each customer:

  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id;
  

4.1.4 Using Aggregate Functions with HAVING

The HAVING clause is used to filter groups based on the result of an aggregate function. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause filters groups after aggregation.

• Syntax:

  SELECT column_name, AGGREGATE_FUNCTION(column_name)
  FROM table_name
  GROUP BY column_name
  HAVING AGGREGATE_FUNCTION(column_name) condition;
  

• Example:
  Find departments where the total salary exceeds 500,000:

  SELECT department, SUM(salary) AS total_salary
  FROM employees
  GROUP BY department
  HAVING SUM(salary) > 500000;
  

4.1.5 Combining Multiple Aggregate Functions

You can combine multiple aggregate functions in a single query to calculate different types of aggregates at once. This allows you to summarize various statistics about a dataset in one result set.

• Example:
  Calculate the total salary, average salary, and number of employees in each department:

  SELECT department, 
         SUM(salary) AS total_salary, 
         AVG(salary) AS average_salary, 
         COUNT(*) AS employee_count
  FROM employees
  GROUP BY department;
  

4.1.6 Handling NULL Values in Aggregate Functions

Aggregate functions generally ignore NULL values, except for COUNT(). COUNT() counts non-NULL values in a column, whereas other functions (such as SUM(), AVG(), MAX(), and MIN()) ignore NULL values during calculations.

• Example:
  Count the number of employees who have a salary (ignores NULL salaries):

  SELECT COUNT(salary) AS employee_count
  FROM employees;
  

4.1.7 Real-World Use Cases

1. Financial Reporting:
   Aggregate functions are commonly used in financial systems to summarize data, such as calculating total sales, average revenue, or the highest/lowest financial metrics.
   • Example:

     SELECT SUM(amount) AS total_sales
     FROM sales
     WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';
     

2. Employee Management:
   In HR systems, you might want to calculate the total salary by department or the average age of employees in each team.
   • Example:

     SELECT department, AVG(age) AS average_age
     FROM employees
     GROUP BY department;
     

3. Product Analysis:
   In an e-commerce system, you could calculate the average price of products, the highest-rated product, or the total sales per category.
   • Example:

     SELECT category, MAX(price) AS max_price
     FROM products
     GROUP BY category;
     

4.1.8 Conclusion

Aggregate functions in SQL—SUM(), AVG(), COUNT(), MAX(), and MIN()—are essential for performing calculations and summarizing data. They are particularly useful in reporting, analysis, and generating insights from large datasets. By using these functions with GROUP BY and HAVING, you can aggregate and filter data efficiently to meet your business or analysis needs.