What Are Aggregate Functions in SQL and How Do They Work?

TL;DR

Aggregate functions perform calculations on a group of values and return a single value as output. SQL has five: COUNT, SUM, AVG, MIN, and MAX. All aggregate functions ignore null values except COUNT. Their power increases when combined with GROUP BY and HAVING clauses, saving huge time on large datasets.

Transcript

hello everyone welcome back in this presentation we will focus on aggregate functions in SQL at first let's focus on the theoretical aspects about aggregate functions basically these aggregate functions perform calculations on multiple values let's say if we have a group of values then aggregate functions takes these group of values as an input and... Read More

Key Insights

• Aggregate functions take a group of multiple values as input and return a single value as output. This is why they suit real-world needs like calculating a single Google Maps ETA from many collected data points.
• Aggregate functions ignore null values in every case except the COUNT function. This distinction matters when a column contains missing data and you want accurate totals or averages.
• COUNT returns the number of rows in a table and works on both numeric and non-numeric data types. Using COUNT(*) on a four-row student table returns four.
• COUNT(DISTINCT column) returns the number of unique values in a column. For an age column holding 25, 24, 25, 32, COUNT(DISTINCT age) returns three because the repeated 25 is skipped.
• SUM returns the total of a selected column and works only on numeric data types. SUM(salary) on the student table returns 23,000, adding every value in the salary column.
• SUM(DISTINCT column) totals only unique values. For ages 25, 24, 25, 32, plain SUM returns 106 while SUM(DISTINCT age) returns 81 by skipping the repeated 25.
• AVG returns the average of a column's values by adding them and dividing by the count. AVG(age) over 25, 24, 25, 32 gives 26, while AVG(DISTINCT age) gives 27.
• MIN returns the lowest value and MAX returns the highest value in a selected column, both working on non-null values only. On the salary column, MIN returns 35,000 and MAX returns 68,000.

Questions & Answers

Q: What is an aggregate function in SQL?

An aggregate function performs a calculation on a group of multiple values and returns a single value as output. Instead of processing one value at a time, it takes many values as input and condenses them into one result. SQL provides five aggregate functions: COUNT, SUM, AVG, MIN, and MAX. They are essential for summarizing large amounts of data efficiently rather than searching manually.

Q: Why do we need aggregate functions?

Aggregate functions are needed to turn many values into one meaningful result for everyday activities. For example, Google Maps collects data from various sources and aggregates it into a single ETA value. A chemical plant temperature sensor records readings every millisecond, but storing them all would exhaust database memory, so values are aggregated into a single stored value. They also save huge time when searching thousands of rows.

Q: What are the five aggregate functions in SQL?

SQL has five aggregate functions. COUNT returns the number of rows in a table. SUM returns the total of the selected column. AVG returns the average value of the selected column. MIN returns the lowest value in a column. MAX returns the highest value in a column. Their power can be further enhanced when combined with the GROUP BY and HAVING clauses.

Q: How does the COUNT function work in SQL?

The COUNT function returns the number of rows in a database table and works on both numeric and non-numeric data types. Using SELECT COUNT(*) FROM student on a table with four rows returns four. You can also count values in a specific column such as COUNT(age). To count only unique values, use COUNT(DISTINCT age), which returns three when the age column holds 25, 24, 25, and 32.

Q: What is the difference between COUNT and COUNT DISTINCT?

COUNT counts all values or rows, including repeated ones, while COUNT with the DISTINCT keyword counts only unique values. For an age column containing 25, 24, 25, and 32, a plain COUNT(age) returns four because it counts every value. COUNT(DISTINCT age) returns three because the repeated 25 is skipped, leaving only the unique values 25, 24, and 32.

Q: How do SUM and AVG functions work in SQL?

SUM returns the total of a selected numeric column, and AVG returns its average. SUM works only on numeric data types; SUM(salary) on the student table returns 23,000. AVG adds all values and divides by their count, so AVG(age) over 25, 24, 25, 32 returns 26. Adding DISTINCT changes results: SUM(DISTINCT age) returns 81 and AVG(DISTINCT age) returns 27 by ignoring the repeated value.

Q: How do you find the minimum and maximum values in a SQL column?

Use the MIN function to return the lowest value and the MAX function to return the highest value in a selected column. Both work on non-null values only. For example, SELECT MIN(salary) FROM student returns 35,000 because that is the smallest salary, and SELECT MAX(salary) FROM student returns 68,000 because that is the largest salary in the column.

Q: Do aggregate functions ignore null values?

Yes, aggregate functions ignore null values in every case except the COUNT function. This means SUM, AVG, MIN, and MAX skip nulls when performing their calculations, so missing data does not distort the result. COUNT is the exception because it counts rows or values regardless. This behavior is important to remember when a column contains missing or empty entries in the data.

Summary & Key Takeaways

• Aggregate functions perform calculations on multiple values and return one result. Real examples include Google Maps aggregating data into a single ETA, and a chemical plant temperature sensor aggregating readings before storing them so the database memory does not exhaust from millisecond-by-millisecond values.

• SQL has five aggregate functions: COUNT, SUM, AVG, MIN, and MAX. COUNT returns the number of rows and works on any data type, while SUM and AVG work on numeric data. All ignore null values except COUNT. The DISTINCT keyword restricts them to unique values.

• Using a student table with ID, name, age, salary, and gender columns, the video demonstrates each function. COUNT(*) returns 4, SUM(salary) returns 23,000, AVG(age) returns 26, MIN(salary) returns 35,000, and MAX(salary) returns 68,000. Power grows with GROUP BY and HAVING clauses.