Mastering SQL Aggregate Functions: SUM, AVG, MIN, MAX, and COUNT

Welcome to this comprehensive guide on SQL aggregate functions. Whether you are new to SQL or need a refresher, you are in the right place for an easy-to-follow overview of these essential functions.

Introduction to SQL Aggregate Functions

Aggregate functions in SQL are used to perform calculations on multiple rows of a table's column and return a single value. These functions are vital for data analysis, allowing you to summarize and extract meaningful information from your datasets.

The key aggregate functions covered in this guide are:

• SUM
• AVG
• MIN
• MAX
• COUNT including COUNT(DISTINCT)

Getting Started

To follow along with the examples, you can download the free SQL cheat sheet. This sheet includes all the queries demonstrated. The code itself can be copied from the accompanying Github repository.

SUM Function

The SUM function adds the values of a specified column. Here’s how it works:

AVG Function

The AVG function calculates the average value of a column:

This query returns the average salary of all employees. For instance, if the total salary is 316,000 and there are five employees, the average salary would be 63,800.

MIN and MAX Functions

The MIN and MAX functions retrieve the minimum and maximum values of a column, respectively. These functions are straightforward to use:

This query will return the lowest and highest salary values in the employee table.

COUNT and COUNT DISTINCT Functions

The COUNT function counts the number of rows that match a specified condition, while COUNT DISTINCT counts the unique values in a column.

Basic COUNT

This query counts the number of non-null salary entries.

COUNT DISTINCT

Here, the first COUNT function counts all non-null entries in the first_name column. The COUNT DISTINCT function counts only unique first names, excluding duplicates.

Conclusion

Understanding and using SQL aggregate functions is crucial for effective data analysis. These functions allow you to summarize and gain insights from your data quickly. In the next video, the focus will be on the GROUP BY functionality, which, when combined with aggregate functions, enables you to calculate subtotals and analyze data categories more granularly.

Stay tuned for the next part of this series and remember to download the free SQL cheat sheet from the link provided below. Happy querying!