• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL COUNT, AVG, SUM

← Previous Next →

In MySQL, COUNT, AVG, and SUM are aggregate functions used to summarize and analyze data. These functions are fundamental for generating reports, calculating totals, averages, and understanding large datasets without manually inspecting every row.

  • COUNT counts the number of rows or non-NULL values.

  • SUM calculates the total of numeric values in a column.

  • AVG finds the average of numeric values.

These functions are widely used in real-world scenarios such as tracking student performance, analyzing sales data, or summarizing employee salaries.

COUNT

The COUNT function is used to find the number of rows that match a certain condition or to count all rows in a table.

Syntax

SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;

  • COUNT(column_name) counts non-NULL values in a column.

  • COUNT(*) counts all rows, including those with NULL values.

Example

SELECT COUNT(*) FROM students;
SELECT COUNT(age) FROM students;

  • The first query counts all students in the table.

  • The second query counts only those students whose age column has a value (ignores NULLs).

Using COUNT with WHERE

You can filter rows before counting using WHERE:

SELECT COUNT(*) FROM students
WHERE city = 'Mumbai';

  • Counts the number of students living in Mumbai.

Using COUNT with DISTINCT

If you want to count unique values, use DISTINCT:

SELECT COUNT(DISTINCT city) FROM students;

  • Counts the number of different cities where students live.

SUM

The SUM function calculates the total of numeric values in a column.

Syntax

SELECT SUM(column_name) FROM table_name;

  • Ignores NULL values automatically.

Example

SELECT SUM(age) FROM students;

  • Returns the total age of all students.

SUM with WHERE

You can filter which rows to sum:

SELECT SUM(age) FROM students
WHERE city = 'Delhi';

  • Returns the total age of students living in Delhi.

SUM with GROUP BY

To calculate totals for each category, combine SUM with GROUP BY:

SELECT city, SUM(age) AS total_age
FROM students
GROUP BY city;

  • Returns the total age for students in each city.

  • Using AS gives a readable column name for the result.

AVG

The AVG function calculates the average value of a numeric column.

Syntax

SELECT AVG(column_name) FROM table_name;

  • Ignores NULL values automatically.

Example

SELECT AVG(age) FROM students;

  • Returns the average age of all students.

AVG with WHERE

Filter before calculating the average:

SELECT AVG(score) FROM students
WHERE city = 'Pune';

  • Returns the average score of students in Pune.

AVG with GROUP BY

To calculate averages for each category:

SELECT city, AVG(score) AS avg_score
FROM students
GROUP BY city;

  • Returns the average score for students in each city.

Combining COUNT, SUM, and AVG

You can use these functions together in a single query to produce a summary report:

SELECT city,
       COUNT(*) AS total_students,
       SUM(age) AS total_age,
       AVG(age) AS avg_age
FROM students
GROUP BY city;

  • Shows the number of students, total age, and average age per city.

  • This type of query is often used in dashboard summaries or statistical reports.

Real-Life Scenarios

Suppose the students table contains:

id name age city score
1 Aditi 19 Mumbai 85
2 Meera 20 Pune 90
3 Pooja 18 Pune 88
4 Riya 21 Delhi 95
5 Ananya 22 Mumbai NULL

Example 1 – Count all students:

SELECT COUNT(*) FROM students;

  • Result: 5 (all rows counted).

Example 2 – Count students with age defined:

SELECT COUNT(age) FROM students;

  • Result: 5 (all ages are defined; NULLs ignored).

Example 3 – Total age of students in Mumbai:

SELECT SUM(age) FROM students
WHERE city = 'Mumbai';

  • Result: 41 (19 + 22)

Example 4 – Average score of students in Pune:

SELECT AVG(score) FROM students
WHERE city = 'Pune';

  • Result: 89 (average of 90 and 88)

Example 5 – Summary by city:

SELECT city,
       COUNT(*) AS total_students,
       SUM(score) AS total_score,
       AVG(score) AS avg_score
FROM students
GROUP BY city;

  • Mumbai → total_students = 2, total_score = 85, avg_score = 85

  • Pune → total_students = 2, total_score = 178, avg_score = 89

  • Delhi → total_students = 1, total_score = 95, avg_score = 95

Example 6 – Count distinct cities:

SELECT COUNT(DISTINCT city) FROM students;

  • Result: 3

Important Points

  1. NULL values are ignored by COUNT(column_name), SUM, and AVG.

  2. COUNT(*) includes all rows, even with NULLs.

  3. Use WHERE to filter data before applying aggregate functions.

  4. Use GROUP BY to get results for different categories or groups.

  5. Use DISTINCT to consider only unique values in counts or sums.

  6. These functions are essential for data analysis, reporting, and making informed decisions.

Summary of the Tutorial

  • COUNT counts rows or non-NULL values.

  • SUM adds numeric values in a column.

  • AVG calculates the average of numeric values.

  • Combine them with WHERE, GROUP BY, and DISTINCT for more powerful analysis.

  • These functions are widely used for summarizing large datasets, creating dashboards, and generating reports in real-world applications.

Using COUNT, SUM, and AVG effectively allows you to quickly understand your data and extract meaningful insights.

Practice Questions

  1. Count the total number of students in the students table.

  2. Count the number of students who have a defined age.

  3. Count the number of students living in ‘Mumbai’.

  4. Count the number of distinct cities in the students table.

  5. Calculate the total age of all students.

  6. Calculate the total score of students living in ‘Pune’.

  7. Calculate the average age of all students.

  8. Calculate the average score of students living in ‘Delhi’.

  9. Generate a report showing count, total age, and average age of students grouped by city.

  10. Calculate the average age of students whose age is greater than 18.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top