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.

 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

 COUNT() Function

 Purpose

Returns the number of rows.

Syntax

Examples

Count all rows

Count non-NULL values

 Difference:

  • COUNT(*) → counts all rows

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

 AVG() Function

 Purpose

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

Syntax

Example

  •  NULL values are ignored automatically.

 SUM() Function

 Purpose

Returns the total sum of a numeric column.

Syntax

SUM(column_name)

Example

 Using COUNT, AVG, SUM with GROUP BY

Department-wise Employee Count

Department wise Average Salary

Department-wise Total Salary

 Using Aggregate Functions with WHERE

  • WHERE filters rows before aggregation.

 Using Aggregate Functions with HAVING

  • HAVING filters groups after aggregation.

 Aggregate Functions & NULL Values

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

 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?

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...