AGGREGATE FUNCTIONS

This lesson on AGGREGATE FUNCTIONS is hands-on and example-driven. You will learn how to use aggregate functions to summarize large datasets into single, meaningful values. You will be able to calculate totals, averages, and counts, and identify extreme values using standard SQL syntax. This skill is foundational for generating reports and performing data analysis.

What You'll Be Able To Do

• Define the purpose of aggregate functions and how they differ from scalar functions.
• Write COUNT() queries to accurately count total rows and count distinct values within a column.
• Calculate totals and averages using SUM() and AVG() on appropriate numeric columns.
• Identify minimum and maximum values using MIN() and MAX() across various data types.
• Explain the critical rule regarding how standard aggregate functions handle NULL values.

Detailed Concept Walkthrough

1. Aggregates: Single Value Summary

Aggregate functions process a set of input values (a column or group) and collapse them into a single output value. They are essential for summarizing large datasets quickly.

• Mechanism: They operate vertically across rows, unlike scalar functions which operate horizontally on a single row.
• Under the Hood: The database engine scans the relevant column(s) for the entire result set (or group) before calculating the final result.
• Syntax Rule: Aggregate functions are typically used in the SELECT list, often without a GROUP BY clause if summarizing the entire table.
• Best Practice: Always alias aggregate results (e.g., COUNT(*) AS total_rows) for clarity in the output.

SELECT COUNT(*) AS total_students FROM student;

Key Takeaway: Aggregates transform many rows of data into one summary row.

2. COUNT() and DISTINCT Keyword

COUNT() determines the number of rows that meet a specified criteria. It is the only aggregate function that can count non-numeric data types and has special NULL handling.

• Mechanism: COUNT(*) counts all rows, including those containing NULLs. COUNT(column) counts only non-NULL values in that specific column.
• Syntax Rule: The DISTINCT keyword placed inside COUNT(DISTINCT column) ensures that only unique, non-NULL values are included in the final tally.
• Under the Hood: When DISTINCT is used, the engine must first sort or hash the column values to identify and eliminate duplicates before counting.
• Example Usage: COUNT(age) counts how many students have a recorded age; COUNT(DISTINCT age) counts how many unique age values exist.

SELECT COUNT(age), COUNT(DISTINCT age) FROM student;

Key Takeaway: Use COUNT(*) for total rows and COUNT(column) to check for non-NULL population in a specific attribute.

3. SUM() and AVG() Calculations

SUM() calculates the total of all values in a column, while AVG() calculates the arithmetic mean (SUM divided by COUNT of non-NULL values).

• Constraint: Both functions strictly require numeric data types (integers, decimals, floats) for the input column.
• Mechanism: AVG() is mathematically equivalent to SUM(column) / COUNT(column). Since both ignore NULLs, the average calculation is accurate only for non-NULL entries.
• Syntax Rule: The DISTINCT keyword can be used with both SUM(DISTINCT col) and AVG(DISTINCT col) to calculate the total or average of only the unique values present.
• Best Practice: Always verify the data type of the column before attempting to use SUM() or AVG().

SELECT SUM(salary), AVG(salary), AVG(DISTINCT age) FROM student;

Key Takeaway: SUM() and AVG() are restricted to numeric columns and automatically exclude NULLs from the calculation.

4. MIN() and MAX() Extreme Values

MIN() and MAX() identify the lowest and highest values, respectively, within a specified column.

• Flexibility: These functions work on almost all data types, including numbers, dates, and text (using alphabetical/lexicographical ordering).
• Mechanism: The engine scans the column, keeping track of the current lowest/highest non-NULL value encountered.
• Under the Hood: For text data, the comparison is based on character set order, meaning 'A' is lower than 'B'.
• NULL Handling: Like most aggregates, they ignore NULL values when determining the minimum or maximum.

SELECT MIN(salary), MAX(age) FROM student;

Key Takeaway: MIN() and MAX() are versatile tools for finding boundaries across numeric, date, and string data.

Topics Covered in AGGREGATE FUNCTIONS

1. Aggregate Function Concept (0:14 - 2:45) — Functions that perform calculations on a group of values to return a single output.
2. COUNT() Function Usage (3:00 - 5:47) — Demonstrating how to count all rows versus counting distinct values using the DISTINCT keyword.
3. SUM() Function (5:50 - 7:56) — Calculating the total value of a numeric column and noting the numeric data type requirement.
4. AVG() Function (7:57 - 9:10) — Calculating the average value, which is derived from SUM divided by the count of non-NULL values.
5. MIN() Function (9:11 - 9:56) — Finding the lowest value in a column, which works across multiple data types.
6. MAX() Function (9:57 - 10:37) — Finding the highest value in a column using the MAX() aggregate function.

SQL Cheat Sheet

• COUNT(*) — Counts all rows in the result set

  SELECT COUNT(*) FROM student;
  

• COUNT(column) — Counts non-NULL values in a column

  SELECT COUNT(age) FROM student;
  

• COUNT(DISTINCT column) — Counts unique, non-NULL values

  SELECT COUNT(DISTINCT age) FROM student;
  

• SUM(column) — Calculates the total sum of numeric values

  SELECT SUM(salary) FROM student;
  

• AVG(column) — Calculates the average of numeric values

  SELECT AVG(age) FROM student;
  

• MIN(column) — Finds the lowest value in the column

  SELECT MIN(salary) FROM student;
  

• MAX(column) — Finds the highest value in the column

  SELECT MAX(salary) FROM student;
  

Comparison Table

Common Pitfalls

• Mistake: Using SUM() or AVG() on a text column. Avoid: Only apply these functions to columns explicitly defined as numeric types.
• Mistake: Assuming COUNT(column) includes rows where the column is NULL. Avoid: Use COUNT(*) if you need the total number of rows regardless of column content.
• Mistake: Forgetting that AVG() only averages non-NULL values. Avoid: If NULLs should be treated as zero, use COALESCE(column, 0) inside the AVG function.

FAQs

• Why does COUNT(*) count rows with NULLs, but COUNT(column) doesn't? COUNT(*) counts the existence of the row itself, while COUNT(column) checks the specific value in that column, which is ignored if NULL.
• Can I use DISTINCT with MIN() or MAX()? Yes, but it usually has no practical effect, as the minimum or maximum value remains the same whether duplicates are considered or not.
• What happens if I try to SUM a text column? The database will usually return an error or implicitly convert the text to zero, as SUM requires a numeric data type for calculation.
• Are aggregate functions related to GROUP BY? Yes, aggregates are often used with the GROUP BY clause, but this lesson focuses only on using them to summarize the entire table.