Count number of rows present in MySQL table using SELECT COUNT(*)

Count the Number of Rows in a MySQL Table

Use the MySQL COUNT() aggregate function to count rows returned by a query. To count every row in a table, run SELECT COUNT(*) FROM table_name;.

You can also combine COUNT() with WHERE, GROUP BY, and DISTINCT to count matching rows, rows in each group, or unique values.

MySQL COUNT(*) Syntax for Counting Table Rows

The following syntax returns the total number of rows in a MySQL table:

SELECT COUNT(*)
FROM table_name;

COUNT(*) counts rows regardless of whether individual columns contain NULL values. The result contains one row with one numeric value.

Give the result column a descriptive alias when the query is used in an application or report:

SELECT COUNT(*) AS total_rows
FROM table_name;

Step 1: Select the MySQL Database

Open mysql command line interface and follow these steps.

You can select the database using USE database;.

USE school;

To confirm the active database before running the count query, use:

SELECT DATABASE();

Step 2: Count All Rows in the students Table

We shall count the total number of rows in students table of school database.

There are two rows in the table. Without printing all the row data, we can get just the number of rows in the table using COUNT keyword.

Run the following query to get the total count of rows present in a table.

SELECT COUNT(*) FROM table_name;

For the students table, replace table_name with students:

SELECT COUNT(*) AS total_students
FROM students;

The query returns a single result row containing the number of records in the table.

+----------------+
| total_students |
+----------------+
|              2 |
+----------------+

Output of the query is just what we need: number of rows present in table.

Count MySQL Rows That Match a WHERE Condition

Add a WHERE clause when only rows meeting a condition should be counted. For example, the following query counts students whose grade is 10:

SELECT COUNT(*) AS grade_10_students
FROM students
WHERE grade = 10;

Conditions can use comparison operators, date ranges, string matching, and multiple expressions joined by AND or OR.

SELECT COUNT(*) AS active_students
FROM students
WHERE status = 'active'
  AND admission_date >= '2026-01-01';

Difference Between COUNT(*), COUNT(column), and COUNT(DISTINCT column)

The argument passed to COUNT() determines what MySQL counts.

Expression	What MySQL counts
COUNT(*)	Every row returned by the query, including rows containing NULL values
COUNT(column_name)	Rows in which the specified column is not NULL
COUNT(DISTINCT column_name)	Distinct non-NULL values in the specified column

For example, suppose some students do not have an email address. The following query returns both the total row count and the number of rows containing a non-NULL email address:

SELECT
    COUNT(*) AS total_students,
    COUNT(email) AS students_with_email
FROM students;

To count the number of different grades represented in the table, use COUNT(DISTINCT ...):

SELECT COUNT(DISTINCT grade) AS distinct_grades
FROM students;

Count Rows in Each MySQL Group

Combine COUNT() with GROUP BY to calculate a separate count for each category. The following query counts students in each grade:

SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade
ORDER BY grade;

Use HAVING when the grouped results should be filtered by an aggregate value. This example returns only grades containing more than five students:

SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade
HAVING COUNT(*) > 5
ORDER BY student_count DESC;

Perform Conditional Counts in MySQL

Conditional aggregation can calculate several counts in one query. A CASE expression returns a value only when its condition is true, and COUNT() ignores the resulting NULL values.

SELECT
    COUNT(*) AS total_students,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_students,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_students
FROM students;

The same calculation can be written with SUM() because MySQL evaluates a true condition as 1 and a false condition as 0:

SELECT
    SUM(status = 'active') AS active_students,
    SUM(status = 'inactive') AS inactive_students
FROM students;

COUNT(*) Versus COUNT(1) in MySQL

Both COUNT(*) and COUNT(1) count rows because the constant value 1 is never NULL. For a query intended to count rows, COUNT(*) is clearer because it directly communicates that every returned row should be counted.

SELECT
    COUNT(*) AS count_star,
    COUNT(1) AS count_one
FROM students;

Do not replace COUNT(*) with COUNT(nullable_column) unless rows containing NULL in that column should be excluded.

Count Rows Returned by a MySQL Query

To count the rows produced by a more complex query, place that query inside a derived table and apply COUNT(*) to its result.

SELECT COUNT(*) AS result_count
FROM (
    SELECT student_id
    FROM students
    WHERE status = 'active'
      AND grade = 10
) AS matching_students;

A direct COUNT(*) with the same WHERE condition is usually simpler. A derived table is useful when the inner query contains grouping, distinct rows, unions, or other operations whose final result set must be counted.

Count the Number of Rows in Every MySQL Table

INFORMATION_SCHEMA.TABLES contains table metadata. The TABLE_ROWS value may be an estimate for storage engines such as InnoDB, so it should not be treated as an exact replacement for SELECT COUNT(*).

SELECT
    TABLE_NAME,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'school'
ORDER BY TABLE_NAME;

Use metadata when an approximate overview of many tables is sufficient. Run SELECT COUNT(*) against each table when exact counts are required.

MySQL COUNT Performance Considerations

Counting all rows in a large table can require MySQL to examine an index or many table records. The time required depends on the storage engine, available indexes, query conditions, server resources, and concurrent workload.

• Add suitable indexes for columns frequently used in WHERE conditions.
• Use EXPLAIN to inspect how MySQL plans a filtered count query.
• Avoid repeatedly calculating an exact full-table count when an approximate or previously maintained count meets the application requirement.
• Measure performance with representative production-sized data rather than assuming that a query fast on a small table will remain fast.

EXPLAIN
SELECT COUNT(*)
FROM students
WHERE status = 'active';

For additional behavior and examples, refer to the MySQL row-counting documentation.

MySQL Row Count QA Checklist

• Confirm that the query uses the intended database and table.
• Use COUNT(*) when all matching rows must be counted, including rows containing NULL values.
• Use COUNT(column_name) only when NULL values in that column should be excluded.
• Verify all WHERE conditions before relying on a filtered row count.
• Check grouped counts for missing categories caused by joins or filters.
• Treat INFORMATION_SCHEMA.TABLES.TABLE_ROWS as an estimate where the storage engine does not maintain an exact value.
• Review the execution plan for count queries that run slowly on large tables.

Frequently Asked Questions About MySQL COUNT()

How do I count all rows in a MySQL table?

Run SELECT COUNT(*) FROM table_name;. Replace table_name with the name of the table whose rows you want to count.

Does MySQL COUNT(*) include rows containing NULL values?

Yes. COUNT(*) counts every row returned by the query. In contrast, COUNT(column_name) excludes rows where that particular column is NULL.

How do I count unique values in a MySQL column?

Use COUNT(DISTINCT column_name). It counts distinct non-NULL values in the specified column.

How do I count MySQL rows for each category?

Use COUNT(*) with GROUP BY. For example, SELECT grade, COUNT(*) FROM students GROUP BY grade; returns one count for each grade.

Is INFORMATION_SCHEMA.TABLES.TABLE_ROWS an exact MySQL row count?

Not always. It can be an estimate for storage engines such as InnoDB. Use SELECT COUNT(*) when the application requires an exact count.

MySQL COUNT(*) Summary

Use COUNT(*) to count every row returned by a MySQL query. Add WHERE for filtered counts, GROUP BY for counts by category, and COUNT(DISTINCT column_name) for unique non-NULL values. Use COUNT(column_name) only when rows containing NULL in that column should not be included.

In this MySQL Tutorial, we have learnt to count total number of rows present in MySQL Table.