12. Aggregate Functions -- COUNT, SUM, AVG, MIN, MAX

Level: Beginner

ℹ️ What You'll Learn

• COUNT -- count rows
• SUM -- sum values
• AVG -- average value
• MIN -- minimum value
• MAX -- maximum value
• COUNT with NULL handling
• Aggregates with WHERE filter
• Multiple aggregates in one query
• Common aggregate mistakes

Queries retrieve data. Aggregates analyze it. This article teaches calculating totals, averages, counts -- essential for reports and dashboards.

Why Aggregates Matter for Backend Developers

APIs need summary data:

Frontend dashboard asks: "How many students? Total fees? Average salary?"
    v
Backend must aggregate database data
    v
COUNT(*) for total students
SUM(Fees) for total fees
AVG(Salary) for average salary
    v
Return summary to dashboard

Without aggregates, backend calculates in code (slow, inefficient).

COUNT Function

Count rows:

SELECT COUNT(*) FROM Student;

Result: 3 (total students)

COUNT(*) vs COUNT(column):

SELECT COUNT(*) FROM Student;           -- 3 (all rows)
SELECT COUNT(ParentPhone) FROM Student; -- 1 (non-NULL phones)

COUNT(*) counts all rows. COUNT(column) ignores NULL.

COUNT with Filter

Count students in class 10-A:

SELECT COUNT(*) FROM Student WHERE ClassName = '10-A';

Result: 2 (Ravi and Priya in 10-A)

SUM Function

Sum values:

SELECT SUM(Fees) FROM Student;

Result: 150000 (total fees from all students)

SUM ignores NULL:

SELECT SUM(Discount) FROM Student;
-- If one Discount is NULL, SUM ignores it

AVG Function

Average value:

SELECT AVG(Salary) FROM Teacher;

Result: 50333.33 (average of 50000, 55000, 45000)

AVG ignores NULL:

SELECT AVG(ISNULL(Discount, 0)) FROM Student;
-- NULL treated as 0 in average

MIN and MAX Functions

Minimum and maximum:

SELECT 
    MIN(Salary) AS LowestSalary,
    MAX(Salary) AS HighestSalary
FROM Teacher;

Result:

LowestSalary | HighestSalary
45000.0      | 55000.0

MIN/MAX work on text too:

SELECT 
    MIN(Name) AS FirstAlphabetically,
    MAX(Name) AS LastAlphabetically
FROM Teacher;

Multiple Aggregates in One Query

Combine multiple aggregates:

SELECT 
    COUNT(*) AS TotalStudents,
    SUM(Fees) AS TotalFees,
    AVG(Fees) AS AverageFees,
    MIN(Fees) AS LowestFees,
    MAX(Fees) AS HighestFees
FROM Student;

Result:

TotalStudents | TotalFees | AverageFees | LowestFees | HighestFees
3             | 150000    | 50000.00    | 50000      | 50000

Aggregate with GROUP BY

Aggregate per group (per class):

SELECT 
    ClassName,
    COUNT(*) AS StudentCount,
    SUM(Fees) AS TotalFees,
    AVG(Fees) AS AvgFees
FROM Student
GROUP BY ClassName;

Result:

ClassName | StudentCount | TotalFees | AvgFees
10-A      | 2            | 100000    | 50000.0
10-B      | 1            | 50000     | 50000.0

Groups by ClassName, aggregates within each group.

Aggregate with HAVING

Filter groups:

SELECT 
    ClassName,
    COUNT(*) AS StudentCount
FROM Student
GROUP BY ClassName
HAVING COUNT(*) > 1;

Result (only classes with >1 student):

ClassName | StudentCount
10-A      | 2

HAVING filters groups (WHERE filters rows before grouping).

---

Common Aggregate Mistakes

Mistake 1: Aggregate without GROUP BY when selecting non-aggregated column

Wrong:

SELECT ClassName, COUNT(*) FROM Student;
-- Error: Non-aggregated column ClassName in select list
-- Which ClassName? Ravi's or Priya's?

Result: Ambiguous. SQL rejects.

Fix: Either aggregate ClassName or GROUP BY it:

SELECT ClassName, COUNT(*) FROM Student GROUP BY ClassName;

Mistake 2: Using WHERE instead of HAVING for groups

Wrong:

SELECT ClassName, COUNT(*) FROM Student
WHERE COUNT(*) > 1;
-- Error: Cannot use aggregate in WHERE

WHERE filters rows before grouping. HAVING filters groups after.

Result: Syntax error.

Fix: Use HAVING:

SELECT ClassName, COUNT(*) FROM Student
GROUP BY ClassName
HAVING COUNT(*) > 1;

Mistake 3: SUM on text column

Wrong:

SELECT SUM(Name) FROM Student;
-- Error: Cannot sum text

SUM only works on numbers.

Result: Error or unexpected behavior.

Fix: Use COUNT instead:

SELECT COUNT(Name) FROM Student;  -- Counts non-NULL names

Mistake 4: Forgetting NULL in COUNT

Wrong:

SELECT COUNT(*) AS Total FROM Student;      -- 3
SELECT COUNT(ParentPhone) AS WithPhone FROM Student;  -- 1
-- Difference not obvious to reader

Result: Confusing results.

Fix: Be explicit:

SELECT 
    COUNT(*) AS TotalStudents,
    COUNT(ParentPhone) AS StudentsWithPhone,
    COUNT(*) - COUNT(ParentPhone) AS StudentsWithoutPhone
FROM Student;

---

Best Practices for Aggregates

1. GROUP BY all non-aggregated columns -- Avoid ambiguity
2. Use HAVING, not WHERE, for group filters -- Correct semantics
3. Handle NULL explicitly -- Use ISNULL in aggregates if needed
4. Test edge cases -- Empty tables, all NULLs, single row
5. Document aggregate purpose -- Comments explain why
6. Use aliases -- COUNT(*) AS TotalCount, not just count result

---

🎯 Q1: What's the difference between COUNT(*) and COUNT(column)?

COUNT(*):

SELECT COUNT(*) FROM Student;  -- 3

Counts ALL rows, including NULLs.

COUNT(column):

SELECT COUNT(ParentPhone) FROM Student;  -- 1

Counts non-NULL values in column.

Example:

StudentId | ParentPhone
1         | 9876543210
2         | NULL
3         | 8765432109

COUNT(*) = 3  (all rows)
COUNT(ParentPhone) = 2  (non-NULL phones)

🎯 Q2: Why does SUM ignore NULL?

NULL = unknown:

1000 + 2000 + NULL + 1500
= 1000 + 2000 + unknown + 1500
= unknown

If discount unknown, total unknown.

SQL pragmatically ignores NULL:

SUM(Discount) = 1000 + 2000 + 1500 = 4500  (ignores NULL)

Otherwise sums would always be NULL.

To include NULL as 0:

SUM(ISNULL(Discount, 0))

🎯 Q3: What's the difference between WHERE and HAVING?

WHERE -- Filter rows BEFORE grouping:

SELECT ClassName, COUNT(*)
FROM Student
WHERE Fees > 50000    -- Filter rows first
GROUP BY ClassName;

HAVING -- Filter groups AFTER grouping:

SELECT ClassName, COUNT(*)
FROM Student
GROUP BY ClassName
HAVING COUNT(*) > 1;  -- Filter groups

Use:

• WHERE: Row-level conditions
• HAVING: Group-level conditions (uses aggregates)

🎯 Q4: Can I use SUM, AVG on text columns?

No, aggregates work on numbers only:

SELECT SUM(Name) FROM Student;
-- Error: Cannot aggregate text

For text use:

• COUNT: COUNT(Name) -> count non-NULL names
• MIN/MAX: MIN(Name) -> alphabetically first/last
• Others: Not applicable

Example:

SELECT 
    COUNT(Name) AS NonNullNames,
    MIN(Name) AS FirstAlpha,
    MAX(Name) AS LastAlpha
FROM Student;

🎯 Q5: How do I aggregate per group?

Use GROUP BY:

SELECT ClassName, COUNT(*) AS StudentCount
FROM Student
GROUP BY ClassName;

Groups rows by ClassName, counts within each group.

Multiple grouping columns:

SELECT ClassName, Section, COUNT(*) AS StudentCount
FROM Student
GROUP BY ClassName, Section;

Groups by both ClassName and Section.

Filter groups:

SELECT ClassName, COUNT(*)
FROM Student
GROUP BY ClassName
HAVING COUNT(*) > 1;  -- Only classes with >1 student

🎯 Q6: What happens when aggregate query returns no rows?

Example:

SELECT SUM(Fees) FROM Student WHERE ClassName = '12-A';
-- No students in 12-A

Result:

• COUNT(*) = 0
• SUM/AVG/MIN/MAX = NULL

Example:

ClassName | COUNT(*) | SUM(Fees) | AVG(Fees)
12-A      | 0        | NULL      | NULL

NULL means "no data to aggregate", not zero.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Aggregate functions in SQL Server for calculations and analysis. Try these prompts:

• "Explain COUNT, SUM, AVG, MIN, MAX with Student/Teacher examples"
• "What's the difference between COUNT(*) and COUNT(column)?"
• "How do I group data and aggregate per group? Show ClassName example"
• "When do I use HAVING instead of WHERE? Show the difference"
• "Quiz me: ask 5 questions about aggregates, grouping, NULL handling"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

13. INNER JOIN -- Connecting Tables ->