SQL COUNT(): Count Rows and Values

The SQL COUNT() aggregate function returns a number. Use COUNT(*) when you want to count rows in a result set.

SELECT COUNT(*) AS total_records
FROM student;

For the Plus2net sample student table, this returns:

SQL COUNT() Syntax Top ↑

To count all rows returned by a query:

SELECT COUNT(*)
FROM table_name;

An alias gives the result a useful column name:

SELECT COUNT(*) AS total_records
FROM student;

COUNT() returns one row containing the aggregate result unless it is combined with GROUP BY.

COUNT() with WHERE Top ↑

Add a WHERE clause when only matching rows should be counted.

SELECT COUNT(*) AS total_records
FROM student
WHERE class = 'Four';

For the sample table, the output is:

COUNT() with Multiple Conditions Top ↑

SELECT COUNT(*) AS total_records
FROM student
WHERE class = 'Four'
  AND mark > 60;

This counts students in class Four whose mark is greater than 60. See AND and OR conditions.

COUNT() with BETWEEN Top ↑

SELECT COUNT(*) AS total_records
FROM student
WHERE class = 'Four'
  AND mark BETWEEN 50 AND 60;

See the BETWEEN tutorial for range conditions.

COUNT(*) vs COUNT(column) Top ↑

SELECT COUNT(*) AS row_count,
       COUNT(class) AS class_value_count
FROM student3;

• COUNT(*) counts every row.
• COUNT(class) counts only rows where class is not NULL.

The older version of this tutorial suggested that counting a unique ID is inherently more efficient than COUNT(*). That should not be treated as a general rule. Use the form that matches the result you need.

COUNT(DISTINCT) for Unique Values Top ↑

SELECT COUNT(DISTINCT class) AS unique_classes
FROM student;

For the sample table, the output is 7. See DISTINCT when you want the unique values themselves.

COUNT() with GROUP BY Top ↑

SELECT class,
       COUNT(*) AS total_students
FROM student
GROUP BY class
ORDER BY class;

Each class becomes one result row with its own count.

Filter Grouped Counts with HAVING Top ↑

SELECT class,
       COUNT(*) AS total_students
FROM student
GROUP BY class
HAVING COUNT(*) > 5;

COUNT() and NULL Values Top ↑

SELECT IFNULL(class,'Not Known') AS class,
       COUNT(*) AS total_rows
FROM student3
GROUP BY class;

For the NULL group, COUNT(*) still counts the rows.

SELECT IFNULL(class,'Not Known') AS class,
       COUNT(class) AS known_class_values
FROM student3
GROUP BY class;

For rows where class is NULL, COUNT(class) returns 0. See SQL NULL values.

COUNT() with LEFT JOIN Top ↑

SELECT product.product,
       COUNT(product_sale.product_id) AS sale_count
FROM product
LEFT JOIN product_sale
  ON product_sale.product_id = product.product_id
GROUP BY product.product_id, product.product
ORDER BY product.product_id;

Counting the right-table key allows unmatched products to show a count of zero. See LEFT JOIN.

Download the SQL dump of the product tables.

Conditional Counts in One Row Top ↑

MySQL can calculate several conditional counts in one query:

SELECT
COUNT(IF(class = 'Three', 1, NULL)) AS THREE,
COUNT(IF(class = 'Four', 1, NULL)) AS FOUR,
COUNT(IF(class = 'Five', 1, NULL)) AS FIVE
FROM student;

This uses the MySQL IF() function. A portable alternative uses CASE with SUM().

Count Records between Two Dates Top ↑

SELECT COUNT(*) AS no_tickets
FROM main_table
WHERE DATE_PL BETWEEN '2026-09-01'
                  AND '2026-09-30';

If dates come from PHP or a form, validate them and bind them as prepared-statement parameters rather than inserting variables directly into SQL text.

Get a COUNT() Result in PHP PDO Top ↑

<?php
require 'config.php';

$class='Four';

$stmt=$dbo->prepare(
    "SELECT COUNT(*)
     FROM student
     WHERE class=:class"
);

$stmt->bindValue(
    ':class',
    $class,
    PDO::PARAM_STR
);

$stmt->execute();

$total=(int)$stmt->fetchColumn();

echo 'Number of records: '.$total;

For a SELECT count, this is more portable than relying on PDO rowCount(). See PDO rowCount().

COUNT() Performance Notes Top ↑

• Use COUNT(*) when the question is "how many rows?"
• Use COUNT(column) when the question is "how many non-NULL values?"
• Use COUNT(DISTINCT column) when the question is "how many unique non-NULL values?"
• Indexes can help selective WHERE and JOIN conditions, depending on the query and data.
• For expensive queries on large tables, inspect the execution plan rather than assuming one COUNT form is always faster.

SQL COUNT() Video Top ↑

Common SQL COUNT() Mistakes Top ↑

Using COUNT(column) when NULL rows must be counted Top ↑

COUNT(column) ignores NULL values. Use COUNT(*) when every row should contribute to the total.

Using COUNT(*) on the right side of a LEFT JOIN Top ↑

If you want zero for unmatched child rows, count a non-NULL key from the child table.

Using WHERE to filter an aggregate result Top ↑

Use WHERE before grouping to filter rows. Use HAVING when filtering grouped results such as COUNT(*) > 5.

Fetching all rows in PHP only to count them Top ↑

If only the count is needed, let SQL return the aggregate instead of transferring every matching row to PHP.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: