SQL Aggregation Functions – Summarizing Data

Lesson 9: SQL Aggregation Functions – Summarizing Data

Estimated Read Time: 20-25 minutes

Objective: In this lesson, you will learn how to use SQL aggregation functions to summarize data. You will explore the most common aggregation functions like COUNT, SUM, AVG, MIN, and MAX.

1. What are Aggregation Functions?

SQL aggregation functions are used to perform calculations on multiple rows of a table’s column and return a single value. These functions help in summarizing or grouping data in a meaningful way.

2. Common Aggregation Functions

Here are the most commonly used aggregation functions in SQL:

• COUNT: Returns the number of rows in a table or the number of non-NULL values in a column.
• SUM: Returns the total sum of a numeric column.
• AVG: Returns the average value of a numeric column.
• MIN: Returns the smallest value in a column.
• MAX: Returns the largest value in a column.

3. Using 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.

Syntax of COUNT:

SELECT COUNT(column_name)
FROM table_name;

Example:

Query: To count the total number of employees in the Employees table:

SELECT COUNT(EmployeeID)
FROM Employees;

Output:

COUNT(EmployeeID)
3

4. Using SUM Function

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

Syntax of SUM:

SELECT SUM(column_name)
FROM table_name;

Example:

Query: To find the total salary of all employees in the Employees table:

SELECT SUM(Salary)
FROM Employees;

Output:

SUM(Salary)
35000

5. Using AVG Function

The AVG function is used to calculate the average value of a numeric column.

Syntax of AVG:

SELECT AVG(column_name)
FROM table_name;

Example:

Query: To find the average salary of employees:

SELECT AVG(Salary)
FROM Employees;

Output:

AVG(Salary)
11666.67

6. Using MIN and MAX Functions

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

Syntax of MIN:

SELECT MIN(column_name)
FROM table_name;

Syntax of MAX:

SELECT MAX(column_name)
FROM table_name;

Example:

Query: To find the lowest salary in the Employees table:

SELECT MIN(Salary)
FROM Employees;

Output:

MIN(Salary)
10000

Query: To find the highest salary in the Employees table:

SELECT MAX(Salary)
FROM Employees;

Output:

MAX(Salary)
12000

7. Using GROUP BY with Aggregation Functions

Aggregation functions are often used with the GROUP BY clause. The GROUP BY clause groups rows that have the same values into summary rows, like finding the number of employees in each department.

Syntax of GROUP BY:

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

Example:

Query: To find the total salary for each department:

SELECT DepartmentID, SUM(Salary)
FROM Employees
GROUP BY DepartmentID;

Output:

DepartmentID | SUM(Salary)
101          | 20000
102          | 15000

8. HAVING Clause with Aggregation Functions

The HAVING clause is used to filter records that are grouped together by the GROUP BY clause. It is similar to the WHERE clause, but WHERE cannot be used with aggregate functions.

Syntax of HAVING:

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

Example:

Query: To find the departments where the total salary is greater than 18000:

SELECT DepartmentID, SUM(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary) > 18000;

Output:

DepartmentID | SUM(Salary)
101          | 20000

9. Practice Exercises

Try these exercises to reinforce your understanding of aggregation functions:

• Write a query to calculate the average salary of employees in each department.
• Write a query to find the total number of employees who earn more than 10000.
• Write a query to get the department with the highest total salary.
• Write a query to get the average salary for employees who belong to a specific department.

10. Conclusion

In this lesson, you learned how to use SQL aggregation functions to summarize data. You explored the COUNT, SUM, AVG, MIN, and MAX functions and how to apply them with the GROUP BY and HAVING clauses. Aggregation functions are powerful tools for summarizing and analyzing your data efficiently.

Key Takeaways:

• COUNT: Returns the number of rows or non-NULL values.
• SUM: Returns the total sum of a numeric column.
• AVG: Returns the average value of a numeric column.
• MIN: Returns the smallest value in a column.
• MAX: Returns the largest value in a column.
• GROUP BY: Groups rows for aggregate functions.
• HAVING: Filters groups based on aggregate function results.