MySQL COUNT() AVG() and SUM() Functions

MySQL Tutorial

📊 MySQL COUNT() , AVG() and SUM() Functions

In MySQL, aggregate functions like COUNT(), AVG(), and SUM() are used to analyze data by performing calculations on multiple rows and returning one result per group.

👉 These are must-know functions for reports, analytics, exams, and interviews.

1️⃣ What are Aggregate Functions?

Aggregate functions:

  • Work on multiple rows

  • Return a single value

  • Commonly used with GROUP BY and HAVING

📌 The most important ones:

  • COUNT() → total rows

  • AVG() → average value

  • SUM() → total value

2️⃣ COUNT() Function ⭐

🔹 Purpose

Returns the number of rows.

Syntax

COUNT(expression)

Examples

Count all rows

SELECT COUNT(*) FROM students;

Count non-NULL values

SELECT COUNT(marks) FROM students;

📌 Difference:

  • COUNT(*) → counts all rows

  • COUNT(column) → counts non-NULL values only

3️⃣ AVG() Function ⭐

🔹 Purpose

Returns the average (mean) value of a numeric column.

Syntax

AVG(column_name)

Example

SELECT AVG(marks) FROM students;

📌 NULL values are ignored automatically.

4️⃣ SUM() Function ⭐

🔹 Purpose

Returns the total sum of a numeric column.

Syntax

SUM(column_name)

Example

SELECT SUM(salary) FROM employees;

5️⃣ Using COUNT, AVG, SUM with GROUP BY ⭐

Department-wise Employee Count


Department wise Average Salary


Department-wise Total Salary


6️⃣ Using Aggregate Functions with WHERE


📌 WHERE filters rows before aggregation.

7️⃣ Using Aggregate Functions with HAVING ⭐


📌 HAVING filters groups after aggregation.

8️⃣ Aggregate Functions & NULL Values ⭐

Function NULL Handling
COUNT(*) Counts NULL
COUNT(column) Ignores NULL
AVG() Ignores NULL
SUM() Ignores NULL

9️⃣ Common Mistakes ❌

❌ Using aggregate functions in WHERE
❌ Forgetting GROUP BY
❌ Expecting AVG/SUM to count NULL
❌ Confusing COUNT(*) with COUNT(column)

📌 Interview Questions & MCQs (Very Important)

Q1. What does COUNT(*) do?

A) Counts non-NULL values
B) Counts NULL values only
C) Counts all rows
D) Counts columns

✅ Answer: C

Q2. What does COUNT(column_name) do?

A) Counts all rows
B) Counts non-NULL values
C) Counts NULL values
D) Counts duplicate values

✅ Answer: B

Q3. Which function returns average value?

A) SUM()
B) COUNT()
C) AVG()
D) MEAN()

✅ Answer: C

Q4. Which function returns total value?

A) TOTAL()
B) SUM()
C) ADD()
D) COUNT()

✅ Answer: B

Q5. Do AVG() and SUM() include NULL values?

A) Yes
B) No

✅ Answer: B

Q6. Which clause filters aggregated results?

A) WHERE
B) GROUP BY
C) HAVING
D) ORDER BY

✅ Answer: C

Q7. Which is correct?

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

A) Correct
B) Incorrect

✅ Answer: A

🔥 Real-Life Use Cases

✔ Total sales calculation
✔ Average marks / salary
✔ Counting users / orders
✔ Department-wise reports
✔ Business analytics dashboards

✅ Summary

  • COUNT() → counts rows

  • AVG() → calculates average

  • SUM() → calculates total

  • Work with GROUP BY & HAVING

  • Ignore NULL values (except COUNT(*))

  • Extremely important for SQL exams & interviews

You may also like...