Lec-79: How Do SQL Aggregate Functions SUM, AVG, COUNT, MIN, and MAX Work in DBMS?

TL;DR

SQL aggregate functions summarize table data: MAX and MIN find extreme values, COUNT measures rows or defined values, SUM adds values, and AVG calculates the mean. In the Emp example, COUNT(*) returns 6, while COUNT(salary) returns 5 because the NULL salary is excluded; DISTINCT further reduces the salary count to 4 unique values. Read on to see how NULL and DISTINCT affect each calculation.

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. They find the maximum, minimum, count, average, and sum of selected data. These functions can be used in simple queries as well as group-by, nested, and correlated queries.

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

MAX(salary) returns the highest available salary, while MIN(salary) returns the lowest available salary. For salaries of 10000, 20000, 30000, 30000, 50000, and NULL, the results are 50000 and 10000, respectively. The NULL value is excluded.

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

COUNT(*) counts every row in the table, so it returns 6 for the Emp example. COUNT(salary) counts only rows with an available salary and returns 5 because one salary is NULL. The first counts rows, while the second counts defined column values.

Q: How does NULL affect SQL aggregate functions?

NULL means a value is unavailable or undefined; it does not mean zero. Named-column aggregate calculations leave NULL out, so MIN(salary) returns 10000 and COUNT(salary) returns 5. The salary sum and average likewise use only the defined values.

Q: How does DISTINCT change COUNT(salary)?

DISTINCT restricts the count to unique defined salary values. In the example, 30000 appears twice but contributes only once, and NULL is not counted. The distinct salary count is therefore 4.

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

SUM(salary) adds the available salaries and excludes NULL. Adding 10000, 20000, 30000, 30000, and 50000 produces 140000. The duplicate 30000 contributes twice because DISTINCT is not applied.

Q: How does DISTINCT change the SUM of salaries?

A distinct salary sum includes each unique defined salary once. The calculation uses 10000, 20000, 30000, and 50000, producing 110000. Without DISTINCT, the duplicate 30000 is also included and the sum is 140000.

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

The ordinary average uses SUM(salary) divided by COUNT(salary), which is 140000 divided by 5 in the example. With DISTINCT, the calculation uses the distinct sum and count: 110000 divided by 4. NULL is excluded from both versions.

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.