Difficulty: Advanced
Aggregation functions are the backbone of analytical SQL. They collapse multiple rows into a single summary value, allowing you to answer questions like 'How much total revenue did each product generate?' or 'Which department has the highest average salary?' Understanding aggregation is critical because most business questions require summarizing data rather than looking at individual records.
The five core aggregation functions are COUNT (number of rows), SUM (total of numeric values), AVG (arithmetic mean), MIN (smallest value), and MAX (largest value). COUNT(*) counts all rows including those with NULLs, while COUNT(column) counts only non-NULL values in that column. This distinction is important for accurate analytics, especially when dealing with datasets that have missing values.
GROUP BY is the clause that transforms aggregation from whole-table summaries to per-group summaries. When you write GROUP BY department, the database partitions all rows by department and applies the aggregation function independently within each partition. Every column in the SELECT that is not inside an aggregation function must appear in the GROUP BY clause -- this is a fundamental rule that trips up many beginners.
HAVING is the post-aggregation filter. While WHERE filters individual rows before aggregation, HAVING filters groups after aggregation. For example, 'find departments where the average salary exceeds 80000' requires HAVING because the average does not exist until after the GROUP BY computation. The execution order is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.
# Simulating aggregate functions on a sales dataset
sales = [
{'product': 'Laptop', 'quantity': 5, 'revenue': 4995},
{'product': 'Phone', 'quantity': 12, 'revenue': 8388},
{'product': 'Tablet', 'quantity': 8, 'revenue': 3192},
{'product': 'Laptop', 'quantity': 3, 'revenue': 2997},
{'product': 'Phone', 'quantity': 7, 'revenue': 4893},
{'product': 'Laptop', 'quantity': 2, 'revenue': 1998},
]
# SQL: SELECT COUNT(*), SUM(revenue), AVG(revenue), MIN(revenue), MAX(revenue) FROM sales
revenues = [s['revenue'] for s in sales]
print("Aggregate Functions (all rows):")
print(f" COUNT: {len(revenues)}")
print(f" SUM: ${sum(revenues):,}")
print(f" AVG: ${sum(revenues)/len(revenues):,.0f}")
print(f" MIN: ${min(revenues):,}")
print(f" MAX: ${max(revenues):,}")These five functions summarize the entire revenue column into single values. COUNT gives the number of transactions, SUM the total revenue, AVG the average per transaction, and MIN/MAX the range. In SQL, these would be written as SELECT COUNT(*), SUM(revenue), AVG(revenue), MIN(revenue), MAX(revenue) FROM sales.
sales = [
{'product': 'Laptop', 'quantity': 5, 'revenue': 4995},
{'product': 'Phone', 'quantity': 12, 'revenue': 8388},
{'product': 'Tablet', 'quantity': 8, 'revenue': 3192},
{'product': 'Laptop', 'quantity': 3, 'revenue': 2997},
{'product': 'Phone', 'quantity': 7, 'revenue': 4893},
{'product': 'Laptop', 'quantity': 2, 'revenue': 1998},
]
# SQL: SELECT product, COUNT(*) as num_sales, SUM(revenue) as total_rev, AVG(revenue) as avg_rev
# FROM sales GROUP BY product ORDER BY total_rev DESC
groups = {}
for s in sales:
p = s['product']
if p not in groups:
groups[p] = []
groups[p].append(s['revenue'])
print("GROUP BY product:")
print(f"{'Product':<10} {'Count':>6} {'Total Rev':>10} {'Avg Rev':>10}")
print("-" * 38)
results = []
for product, revs in groups.items():
results.append((product, len(revs), sum(revs), sum(revs)/len(revs)))
results.sort(key=lambda x: x[2], reverse=True)
for product, count, total, avg in results:
print(f"{product:<10} {count:>6} {total:>10,} {avg:>10,.0f}")GROUP BY partitions the data by product, then applies aggregation functions within each group. Phones have the highest total revenue despite fewer transactions than Laptops. This pattern is the workhorse of analytical SQL -- virtually every business report uses GROUP BY.
employees = [
{'name': 'Alice', 'dept': 'Engineering', 'salary': 95000},
{'name': 'Bob', 'dept': 'Marketing', 'salary': 72000},
{'name': 'Charlie', 'dept': 'Engineering', 'salary': 105000},
{'name': 'Diana', 'dept': 'HR', 'salary': 68000},
{'name': 'Eve', 'dept': 'Marketing', 'salary': 78000},
{'name': 'Frank', 'dept': 'Engineering', 'salary': 88000},
{'name': 'Grace', 'dept': 'HR', 'salary': 65000},
]
# SQL: SELECT dept, COUNT(*) as cnt, AVG(salary) as avg_sal
# FROM employees GROUP BY dept HAVING COUNT(*) >= 2 AND AVG(salary) > 70000
groups = {}
for e in employees:
groups.setdefault(e['dept'], []).append(e['salary'])
print("All departments:")
for dept, salaries in sorted(groups.items()):
print(f" {dept}: count={len(salaries)}, avg=${sum(salaries)/len(salaries):,.0f}")
print("\nHAVING COUNT(*) >= 2 AND AVG(salary) > 70000:")
for dept, salaries in sorted(groups.items()):
count = len(salaries)
avg = sum(salaries) / count
if count >= 2 and avg > 70000:
print(f" {dept}: count={count}, avg=${avg:,.0f}")HAVING filters groups after aggregation. HR is excluded because its average salary ($66,500) does not exceed $70,000, even though it has 2 employees. WHERE filters rows before grouping; HAVING filters groups after. This distinction is one of the most tested SQL concepts in interviews.
orders = [
{'id': 1, 'customer': 'Alice', 'product': 'Laptop', 'coupon': 'SAVE10'},
{'id': 2, 'customer': 'Alice', 'product': 'Mouse', 'coupon': None},
{'id': 3, 'customer': 'Bob', 'product': 'Laptop', 'coupon': 'SAVE10'},
{'id': 4, 'customer': 'Charlie', 'product': 'Keyboard', 'coupon': None},
{'id': 5, 'customer': 'Alice', 'product': 'Monitor', 'coupon': 'SAVE20'},
]
# COUNT(*) -- counts all rows
count_star = len(orders)
# COUNT(coupon) -- counts non-NULL coupon values
count_coupon = sum(1 for o in orders if o['coupon'] is not None)
# COUNT(DISTINCT customer) -- counts unique customers
count_distinct_cust = len(set(o['customer'] for o in orders))
# COUNT(DISTINCT product) -- counts unique products
count_distinct_prod = len(set(o['product'] for o in orders))
print("COUNT Variations:")
print(f" COUNT(*) = {count_star}")
print(f" COUNT(coupon) = {count_coupon}")
print(f" COUNT(DISTINCT customer) = {count_distinct_cust}")
print(f" COUNT(DISTINCT product) = {count_distinct_prod}")COUNT(*) counts all 5 rows. COUNT(coupon) counts only 3 because 2 orders have NULL coupons. COUNT(DISTINCT customer) gives 3 unique customers (Alice appears 3 times but counts once). Understanding these variations is essential for accurate metrics in analytics dashboards.
COUNT, SUM, AVG, GROUP BY, HAVING