Aggregate Functions in SQL

Aggregate functions are special SQL functions used to perform calculations on a group of rows and return a single summarized result. Instead of showing individual records, aggregate functions help us analyze data by providing totals, averages, minimums, maximums, and counts. These functions are heavily used in reporting, dashboards, analytics, and business decision-making.

Aggregate functions usually work on numeric data, but some of them can also work with dates and text (for example, COUNT).

---

Key Characteristics of Aggregate Functions

• They return one value for many rows
• They ignore NULL values (except COUNT(*))
• They are often used with GROUP BY
• They are commonly used in reports and analytics

---

COUNT Function

The COUNT function is used to count the number of rows in a table or the number of non-null values in a column.

---

Counting All Rows

SELECT COUNT(*) AS total_employees
FROM employees;

Explanation

• COUNT(*) counts all rows, including rows with NULL values
• This gives the total number of employees in the table

---

Counting Rows Based on Condition

SELECT COUNT(*) AS sales_count
FROM employees
WHERE department = 'Sales';

Explanation

• WHERE filters rows first
• COUNT then counts only matching rows
• Useful for department-wise or condition-based counts

---

Counting Non-NULL Values

SELECT COUNT(email) AS employees_with_email
FROM employees;

Explanation

• COUNT(column_name) counts only non-null values
• Employees without email are excluded
• Very useful for data completeness checks

---

COUNT Summary

Syntax	What It Counts
COUNT(*)	All rows
COUNT(column)	Non-null values only

---

SUM Function

The SUM function is used to calculate the total of numeric values in a column.

---

Total Salary Expenditure

SELECT SUM(salary) AS total_salary_cost
FROM employees;

Explanation

• Adds all salary values
• Ignores NULL salaries
• Used for budget and payroll analysis

---

Conditional SUM

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

Explanation

• Calculates salary total only for IT department
• WHERE clause filters rows before summation

---

AVG Function

The AVG function calculates the arithmetic mean (average) of numeric values.

---

Average Salary of All Employees

SELECT AVG(salary) AS average_salary
FROM employees;

Explanation

• Adds all salary values
• Divides by number of non-null salary rows
• Useful for salary benchmarking

---

Department-Specific Average

SELECT AVG(salary) AS marketing_avg
FROM employees
WHERE department = 'Marketing';

Explanation

• Calculates average salary only for Marketing department
• Helpful for departmental comparisons

---

MIN and MAX Functions

The MIN and MAX functions are used to find the smallest and largest values in a column.

---

Finding Lowest and Highest Salary

SELECT 
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;

Explanation

• MIN finds the minimum salary
• MAX finds the maximum salary
• Commonly used for range analysis

---

Working with Dates

SELECT 
    MIN(hire_date) AS first_hired,
    MAX(hire_date) AS last_hired
FROM employees;

Explanation

• MIN finds the earliest date
• MAX finds the most recent date
• Useful for tracking employee history

---

Combining Multiple Aggregate Functions

Multiple aggregate functions can be used together in a single query.

SELECT 
    COUNT(*) AS total_employees,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_payroll
FROM employees;

Explanation

This query provides:

• Total number of employees
• Minimum salary
• Maximum salary
• Average salary
• Total payroll cost

This type of query is commonly used in management dashboards.

---

Aggregate Functions with WHERE Clause

• WHERE filters rows before aggregation
• Always applied before aggregate calculation

Example:

SELECT AVG(salary)
FROM employees
WHERE department = 'HR';

---

Aggregate Functions with GROUP BY (Important Concept)

Aggregate functions become much more powerful when combined with GROUP BY.

Example:

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

Explanation

• Groups employees by department
• Calculates average salary for each department
• Very common interview topic

---

Aggregate Functions with HAVING

• HAVING filters after aggregation
• Used with GROUP BY

Example:

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

---

Handling NULL Values in Aggregates

• COUNT(*) includes NULL rows
• COUNT(column), SUM, AVG ignore NULLs
• Use COALESCE() to handle NULLs if needed

Example:

SELECT AVG(COALESCE(salary, 0))
FROM employees;

---

Real-World Use Cases

• Salary analysis
• Employee count by department
• Payroll budgeting
• Business reports
• Performance dashboards
• HR analytics

---

Common Beginner Mistakes

• Using aggregate functions without GROUP BY
• Confusing WHERE and HAVING
• Forgetting NULL handling
• Expecting COUNT(column) to count NULLs