Aggregate Functions in SQL

Aggregate functions in SQL are built-in functions used to perform calculations on a set of values and return a single summarized result. They are often used with the GROUP BY clause to analyze data across groups.

Think of aggregate functions as tools to "summarize" data. For example, if you have a list of numbers, you might want to know their sum, average, maximum, minimum, or how many numbers are in the list.

---

Key Points

1. Summarizes Data:
   Aggregate functions reduce multiple rows into a single result.

2. Commonly Used with GROUP BY:
   They’re most effective when grouping data into categories and calculating metrics for each group.

3. Ignores NULL Values:
   Aggregate functions typically skip NULL values in their calculations.

---

Common Aggregate Functions

---

Syntax

SELECT aggregate_function(column_name)
FROM table_name
[WHERE condition]
[GROUP BY column_name];

---

Examples of Aggregate Functions

1. Counting Rows

SELECT COUNT(*) AS total_employees
FROM employees;

• Counts the total number of rows (employees) in the table.

2. Summing Numeric Data

SELECT SUM(salary) AS total_salary
FROM employees
WHERE department = 'IT';

• Calculates the total salary for all employees in the 'IT' department.

3. Calculating an Average

SELECT AVG(salary) AS average_salary
FROM employees;

• Finds the average salary of all employees.

4. Finding the Maximum

SELECT MAX(salary) AS highest_salary
FROM employees;

• Identifies the highest salary among employees.

5. Finding the Minimum

SELECT MIN(salary) AS lowest_salary
FROM employees;

• Finds the lowest salary among employees.

6. Combining with GROUP BY

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

• Calculates the average salary for each department.

---

When to Use Aggregate Functions

• To summarize data for reports or dashboards.

• To analyze data trends, such as averages or totals.

• To rank and identify top or bottom performers in datasets.

---

Common Mistakes to Avoid

1. Using Non-Aggregated Columns Without Grouping:

    ELECT department, SUM(salary)
    FROM employees; -- Error!
   

   • Non-aggregated columns must either be grouped or excluded.

2. Ignoring NULL Values:

   • Aggregate functions ignore NULL by default, which may affect the results.

---

Tips for Beginners

• Always ask yourself: "What summary information do I need?"

• Pair aggregate functions with GROUP BY for grouped results.