3-6-1 Aggregate Functions Explained

Key Concepts

• Aggregate Functions
• COUNT
• SUM
• AVG
• MIN
• MAX

Aggregate Functions

Aggregate functions in SQL are used to perform calculations on a set of values and return a single value. These functions are essential for summarizing data and extracting meaningful insights from large datasets.

COUNT

The COUNT function returns the number of rows that match a specified condition. It is commonly used to count the number of records in a table or the number of non-NULL values in a column.

Example: To count the number of employees in a company, you would use the following SQL query:

        SELECT COUNT(*)
        FROM Employees;
    

Analogy: Think of COUNT as counting the number of items in a shopping cart.

SUM

The SUM function returns the total sum of a numeric column. It is used to calculate the sum of values in a column, such as the total sales amount or the total salary of employees.

Example: To find the total salary paid to all employees, you would use the following SQL query:

        SELECT SUM(Salary)
        FROM Employees;
    

Analogy: Think of SUM as adding up the total cost of items in a shopping cart.

AVG

The AVG function returns the average value of a numeric column. It calculates the mean of the values in a column, which is useful for finding the average salary, average sales, etc.

Example: To find the average salary of employees, you would use the following SQL query:

        SELECT AVG(Salary)
        FROM Employees;
    

Analogy: Think of AVG as calculating the average score in a test.

MIN

The MIN function returns the smallest value of the selected column. It is used to find the minimum value in a column, such as the lowest salary or the earliest date.

Example: To find the lowest salary among employees, you would use the following SQL query:

        SELECT MIN(Salary)
        FROM Employees;
    

Analogy: Think of MIN as finding the lowest score in a test.

MAX

The MAX function returns the largest value of the selected column. It is used to find the maximum value in a column, such as the highest salary or the latest date.

Example: To find the highest salary among employees, you would use the following SQL query:

        SELECT MAX(Salary)
        FROM Employees;
    

Analogy: Think of MAX as finding the highest score in a test.

Conclusion

Understanding aggregate functions is crucial for performing data analysis and generating reports in SQL. By using COUNT, SUM, AVG, MIN, and MAX, you can extract valuable insights from your data, helping you make informed decisions and gain a deeper understanding of your dataset.