COUNT() Function in MySQL

Introduction
Counting rows and records is fundamental for reporting and analysis.
COUNT() function returns number of rows or non-NULL values matching criteria.
It is widely used for:

  • Summary statistics
  • Data validation
  • Performance monitoring

What is COUNT() Function
COUNT() is aggregate function that counts rows based on specified criteria.
Three main forms: COUNT(*), COUNT(column), COUNT(DISTINCT column).

Basic Syntax

sql

SELECT COUNT(*) FROM table_name; 

SELECT COUNT(column_name) FROM table_name; 

Example

sql

SELECT COUNT(*) FROM Students; 

Returns total number of student records.

COUNT(*) vs COUNT(column)

sql

SELECT COUNT(*) as total_rows, 

       COUNT(marks) as students_with_marks 

FROM Students; 

COUNT(*) counts all rows, COUNT(marks) skips NULLs.

COUNT(DISTINCT)
Counts unique non-NULL values.

sql

SELECT COUNT(DISTINCT city) as unique_cities 

FROM Students; 

COUNT with WHERE
Conditional counting.

sql

SELECT COUNT(*) FROM Students 

WHERE marks > 80; 

COUNT with ORDER BY
Count then sort (rare but valid).

sql

SELECT city, COUNT(*) as student_count 

FROM Students 

GROUP BY city 

ORDER BY student_count DESC; 

COUNT with GROUP BY
Most common pattern.

sql

SELECT city, COUNT(*) as student_count 

FROM Students 

GROUP BY city; 

COUNT with JOIN
Count across relationships.

sql

SELECT c.course_name, COUNT(s.id) as enrollment 

FROM Courses c 

LEFT JOIN Students s ON c.id = s.course_id 

GROUP BY c.id, c.course_name; 

COUNT with HAVING
Filter grouped counts.

sql

SELECT city, COUNT(*) as count 

FROM Students 

GROUP BY city 

HAVING COUNT(*) > 10; 

Multiple COUNT Functions
Multiple aggregations.

sql

SELECT city, 

       COUNT(*) as total_students, 

       COUNT(DISTINCT department) as unique_depts, 

       COUNT(marks) as students_with_marks 

FROM Students 

GROUP BY city; 

COUNT with Subquery
Nested counting.

sql

SELECT ( 

    SELECT COUNT(*) FROM Students WHERE marks > 90 

) as top_performers; 

Execution Order with COUNT
COUNT applied after FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

  • COUNT(*) fastest (uses index)
  • COUNT(column) scans for NULLs
  • COUNT(DISTINCT) slowest (deduplication)
  • Index WHERE columns

Important Notes

  • COUNT(*) counts all rows (including NULLs)
  • COUNT(column) skips NULL values
  • COUNT(DISTINCT) ignores NULLs
  • Empty table = 0 (not NULL)
  • Works with *, column, DISTINCT

Example Scenario
Complete enrollment statistics:

sql

SELECT

    c.course_name,

    COUNT(s.id) as total_enrolled,

    COUNT(DISTINCT s.city) as unique_cities,

    COUNT(CASE WHEN s.marks > 80 THEN 1 END) as high_performers

FROM Courses c

LEFT JOIN Students s ON c.id = s.course_id

GROUP BY c.id, c.course_name

ORDER BY total_enrolled DESC;

Common Mistakes

  • COUNT(column) surprised by NULLs
  • COUNT(DISTINCT) performance hit
  • WHERE vs HAVING confusion
  • GROUP BY without aggregates
  • COUNT(*) on large tables without LIMIT

Key Points to Remember

  • COUNT(*) = total rows, COUNT(col) = non-NULL values
  • COUNT(DISTINCT) = unique non-NULL values
  • GROUP BY + COUNT = category counts
  • HAVING filters grouped counts
  • Index WHERE columns for performance
  • COUNT returns 0 for empty results