🧙 Introduction: The Treasure Chest Wizard
Imagine you are an adventurer who finds a treasure chest containing hundreds of gold coins.
Instead of sitting on the floor counting and weighing every single coin for hours, you summon a Math Wizard:
- You ask: "Wizard, how many coins are there?" The wizard chants:
COUNT! and says: "150 coins." - You ask: "What is the total weight?" The wizard chants:
SUM! and says: "300 ounces." - You ask: "What is the average value?" The wizard chants:
AVG! and says: "2 gold coins." - You ask: "What is the heaviest coin?" The wizard chants:
MAX! and says: "5 ounces."
In SQL, Aggregate Functions are this math wizard! They take multiple rows of data and compute a single summary value from them.
📊 The Big Five Aggregate Functions
Relational databases support these five fundamental math tools:
Multiple Detail Rows Single Summary Value
+----------------------------+
| item_id | name | price |
+---------+--------+---------+
| 1 | Pen | 1.50 |
| 2 | Book | 15.00 | ======> SUM(price) =====> $1,015.50
| 3 | Laptop | 999.00 |
+----------------------------+
COUNT(column): Counts the number of non-null values in a column.SUM(column): Adds up all the values in a numeric column.AVG(column): Computes the average value of a numeric column.MIN(column): Finds the smallest value.MAX(column): Finds the largest value.
💻 Code Examples
Let's write queries to summarize statistics for a table of items.
SQL Aggregate Queries
-- Calculate sum, average, min, and max price of products
SELECT
COUNT(id) AS total_items,
SUM(price) AS total_value,
AVG(price) AS average_price,
MIN(price) AS cheapest_item,
MAX(price) AS most_expensive
FROM inventory;Multi-Language Execution
⚠️ Common Mistakes
1. Combining Aggregate and Non-Aggregate Columns
Running SELECT name, SUM(price) FROM products. The database gets confused because SUM(price) returns one single row, but name represents multiple rows.
- Bad:
SELECT category, AVG(price) FROM products;(Crashes in most database engines). - Good: Use
GROUP BY category.
2. Confusing COUNT(column) vs. COUNT(*)
COUNT(column) ignores rows where that column is NULL. COUNT(*) counts every single row in the table, including empty ones.
🔍 Interview Corner
Q1: How do aggregate functions treat NULL values?
All aggregate functions (like SUM, AVG, MIN, MAX, and COUNT(column)) automatically ignore NULL values when performing calculations. The only exception is COUNT(*) which counts the entire row size, including NULL values.
Q2: What is the difference between COUNT(1) and COUNT(*)?
In modern database engines (like PostgreSQL, MySQL, and SQL Server), there is no difference in performance or result between COUNT(1) and COUNT(*). The query optimizer treats them identically.
📝 Practice Links
Explore related coding challenges on the platform:
📝 Summary
- Aggregate Functions summarize data rows into a single numeric result.
- The main functions are
COUNT,SUM,AVG,MIN, andMAX. - They skip
NULLvalues automatically (exceptCOUNT(*)).