How to Use SQL Aggregate Functions in Queries

TL;DR

SQL aggregate functions summarize column values: MAX finds the highest value, MIN finds the lowest, COUNT measures rows or available values, SUM adds values, and AVG calculates their mean. NULL values are ignored when aggregating a named column, while DISTINCT limits calculations to unique non-NULL values and changes COUNT, SUM, and AVG results.

Transcript

Hello friends! Welcome to Gate Smashers The topic is aggregate functions in SQL Before starting the video, I want to request my viewers That please subscribe my channel And please press the bell button So that you can get latest notifications So today we are going to talk about Aggregate functions in SQL Basically there are 5 aggregate functions Ma... Read More

Key Insights

• SQL has five basic aggregate functions: MAX, MIN, COUNT, AVG, and SUM. These functions summarize values from a table and can be used in simple queries as well as group-by, nested, and correlated queries.
• MAX(salary) returns the highest salary in the employee table. With salaries of 10000, 20000, 30000, 30000, 50000, and NULL, the query SELECT MAX(salary) FROM Emp returns 50000.
• MIN(salary) returns the lowest available salary rather than treating NULL as zero. In the example employee table, SELECT MIN(salary) FROM Emp ignores the unavailable value and returns 10000.
• NULL is an unavailable or undefined value, not automatically zero or any other specific number. Because its numeric value is unknown, named-column aggregate calculations leave it out of the values being summarized.
• COUNT() counts every row in a table, including a row containing a NULL salary. The employee table has 6 rows, so SELECT COUNT() FROM Emp produces a result of 6.
• COUNT(salary) counts only rows with an available salary value. Since one of the 6 employee rows has a NULL salary, SELECT COUNT(salary) FROM Emp returns 5 rather than 6.
• DISTINCT limits an aggregate calculation to unique values and removes duplicate contributions. The defined salaries contain 30000 twice, so the distinct salary count is 4 and the distinct salary sum is 110000.
• AVG(salary) is calculated as SUM(salary) divided by COUNT(salary). The ordinary calculation uses 140000 divided by 5, while the distinct calculation uses the distinct sum of 110000 divided by the distinct count of 4.

Questions & Answers

Q: What are the five aggregate functions in SQL?

The five aggregate functions presented are MAX, MIN, COUNT, AVG, and SUM. MAX finds the largest available value, MIN finds the smallest, COUNT measures rows or defined column values, SUM adds the available numeric values, and AVG calculates their mean. These functions can be used in simple SELECT statements and later within more complex group-by, nested, and correlated queries.

Q: How do MAX and MIN work with a salary column?

MAX and MIN examine the available values in the selected column. For salaries of 10000, 20000, 30000, 30000, 50000, and NULL, SELECT MAX(salary) FROM Emp returns 50000, while SELECT MIN(salary) FROM Emp returns 10000. The NULL entry is not treated as zero because it represents a value that is unavailable or undefined.

Q: What is the difference between COUNT(*) and COUNT(salary)?

COUNT(*) measures the total number of rows in the table, so it returns 6 for the example employee data. COUNT(salary) measures only rows where the salary value is available. Because one of the 6 salary entries is NULL, SELECT COUNT(salary) FROM Emp returns 5. The difference comes from whether the query counts rows or defined column values.

Q: How does NULL affect SQL aggregate functions?

NULL represents a value that is unavailable, empty, or undefined. It does not mean zero, and its unknown value could not be assumed to equal any particular salary. When an aggregate function is applied to the salary column, the NULL entry is left out. Consequently, MIN returns 10000, COUNT(salary) returns 5, and the salary calculations use only defined values.

Q: How does DISTINCT change a COUNT calculation?

DISTINCT restricts the calculation to unique defined values, so repeated salaries are considered once. The example contains salaries of 10000, 20000, 30000, 30000, 50000, and NULL. The duplicate 30000 contributes only once, and NULL is not counted. As a result, the distinct salary count is 4, representing 10000, 20000, 30000, and 50000.

Q: How is SUM(salary) calculated in the employee table?

SUM(salary) adds all available salary values and leaves out the NULL entry. In the example, it adds 10000, 20000, 30000, 30000, and 50000, producing 140000. The repeated salary of 30000 is included twice in the ordinary sum because DISTINCT is not being applied, while the unavailable salary makes no numeric contribution.

Q: How does DISTINCT change the SUM of salaries?

A distinct salary sum adds each unique defined salary only once. The example has available values of 10000, 20000, 30000, 30000, and 50000. Because 30000 appears twice, one occurrence is removed from the distinct calculation. Adding 10000, 20000, 30000, and 50000 gives 110000, compared with the ordinary sum of 140000.

Q: How is AVG(salary) calculated with and without DISTINCT?

The ordinary average salary is calculated as SUM(salary) divided by COUNT(salary). For the example, the sum is 140000 and the count of defined salaries is 5, so the calculation is 140000 divided by 5. With DISTINCT, both parts use unique values: the distinct sum is 110000 and the distinct count is 4, producing 110000 divided by 4.

Summary & Key Takeaways

• SQL provides five basic aggregate functions: MAX, MIN, COUNT, SUM, and AVG. They summarize values from a table and can be written in simple SELECT queries. Understanding their basic behavior is important because the same functions are frequently needed later in group-by, nested, and correlated queries.

• Using the employee table, MAX(salary) returns 50000 and MIN(salary) returns 10000. The NULL salary is ignored because NULL means the value is unavailable, not zero. Aggregate functions therefore calculate results from the defined salary values rather than treating the missing value as a numeric amount.

• COUNT(*) returns all 6 table rows, while COUNT(salary) returns 5 because the NULL salary is excluded. Counting distinct salaries returns 4 unique defined values. SUM(salary) produces 140000, while the distinct salary sum is 110000. AVG uses the corresponding sum divided by the corresponding count.