How to Count Rows in SQL: The Complete Guide (MySQL, PostgreSQL, SQLite, SQL Server)

Your table has 2 billion rows.

You run:

SELECT COUNT(*) FROM orders;

It takes 47 seconds. Your boss asks: "Can we make this faster?"

The answer is yes, but only after you separate two different questions:

• Do you need an exact count?
• Or do you only need a fast estimate?

This guide covers sql count rows patterns from the basic COUNT(*) query to catalog estimates that return in milliseconds on large tables. It also explains COUNT(*) vs COUNT(1), conditional counts, COUNT DISTINCT sql syntax, window functions, and ORM count APIs.

If you exported database rows to CSV and only need a file line count, use the browser-based Line Counter tool. If you need the database to count rows in SQL, keep reading.

Quick Scenario Guide

For most count rows in sql tasks, COUNT(*) is the exact answer. For dashboards, monitoring, and billion-row tables, sql count rows without full scan methods are often the better product decision.

Part 1: COUNT(*) - The Foundation

Basic syntax

Use COUNT(*) when you want to count rows in SQL and include every row, regardless of NULL values.

SELECT COUNT(*) FROM employees;

Give the result a useful name:

SELECT COUNT(*) AS total_employees
FROM employees;

Count rows with a condition:

SELECT COUNT(*) AS engineering_employees
FROM employees
WHERE department = 'Engineering';

Count several conditions in one pass:

SELECT
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive
FROM employees;

That pattern is portable and clear. It is often the best way to keep a sql count rows report in one query instead of running several separate queries.

COUNT(*) vs COUNT(1) vs COUNT(column)

The COUNT(*) vs COUNT(1) argument has been running for decades. The practical answer is simple: on modern MySQL, PostgreSQL, SQL Server, and SQLite systems, COUNT(*) and COUNT(1) are equivalent for row counts.

SELECT COUNT(*) FROM orders;
SELECT COUNT(1) FROM orders;
SELECT COUNT(id) FROM orders;

The first two count rows. The third counts non-NULL id values.

In PostgreSQL, the plans for COUNT(*) and COUNT(1) are typically the same shape:

EXPLAIN SELECT COUNT(*) FROM orders;
-- Aggregate
--   -> Seq Scan on orders

EXPLAIN SELECT COUNT(1) FROM orders;
-- Aggregate
--   -> Seq Scan on orders

MySQL's documentation also states that COUNT(1) is only subject to the same optimization as COUNT(*); there is no portable reason to prefer COUNT(1).

The final COUNT(*) vs COUNT(1) answer is: use COUNT(*). It is standard SQL, readable, and communicates "count all rows." The performance question you should ask is not COUNT(*) vs COUNT(1). It is whether you need an exact count, whether a useful index exists, and whether an estimate would be enough.

Part 2: Counting Rows with Conditions

WHERE clauses

The most common sql count rows where condition pattern is COUNT(*) plus WHERE.

SELECT COUNT(*) FROM orders
WHERE status = 'completed';

SELECT COUNT(*) FROM orders
WHERE status = 'completed'
  AND amount > 100;

SELECT COUNT(*) FROM orders
WHERE status = 'pending'
   OR status = 'processing';

SELECT COUNT(*) FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

SELECT COUNT(*) FROM users
WHERE email LIKE '%@gmail.com';

SELECT COUNT(*) FROM users
WHERE phone IS NULL;

SELECT COUNT(*) FROM users
WHERE phone IS NOT NULL;

For sql count rows where condition queries on large tables, add an index that matches the filter. A count query still has to identify matching rows, and an index can turn a full table scan into an index scan.

Conditional aggregation

Use conditional aggregation when one query should return several counts.

Portable CASE WHEN:

SELECT
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count,
    COUNT(CASE WHEN age >= 18 THEN 1 END) AS adult_count
FROM users;

MySQL can sum Boolean expressions:

SELECT
    COUNT(*) AS total,
    SUM(status = 'active') AS active_count,
    SUM(status = 'inactive') AS inactive_count
FROM users;

PostgreSQL and SQLite 3.30+ support FILTER:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

For portable application SQL, use CASE WHEN. For PostgreSQL analytics, FILTER is often the cleanest sql count rows where condition style.

GROUP BY counts

Use GROUP BY when you need counts per category.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

Monthly order counts differ by database:

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS order_count
FROM orders
GROUP BY month
ORDER BY month;

-- PostgreSQL
SELECT TO_CHAR(created_at, 'YYYY-MM') AS month, COUNT(*) AS order_count
FROM orders
GROUP BY month
ORDER BY month;

-- SQLite
SELECT strftime('%Y-%m', created_at) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY month
ORDER BY month;

-- SQL Server
SELECT FORMAT(created_at, 'yyyy-MM') AS month, COUNT(*) AS order_count
FROM orders
GROUP BY FORMAT(created_at, 'yyyy-MM')
ORDER BY month;

Use HAVING to filter group results:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Part 3: COUNT(DISTINCT) - Count Unique Values

Use COUNT(DISTINCT column) when duplicate values should count once.

SELECT COUNT(DISTINCT country) AS country_count
FROM customers;

SELECT COUNT(DISTINCT user_id) AS buyers
FROM orders;

COUNT DISTINCT sql behavior skips NULL values. That means COUNT(DISTINCT country) counts unique non-NULL countries, not every row.

Multi-column distinct counts are not fully portable:

-- PostgreSQL
SELECT COUNT(DISTINCT (user_id, product_id)) AS unique_pairs
FROM orders;

Portable subquery:

SELECT COUNT(*) AS unique_pairs
FROM (
    SELECT DISTINCT user_id, product_id
    FROM orders
) AS pairs;

Use the subquery when the same SQL must run across MySQL, PostgreSQL, SQLite, and SQL Server. It is also easier for many teams to read than database-specific COUNT DISTINCT sql tuple syntax.

Part 4: COUNT(*) OVER() - Get Total Count Without a Second Query

Pagination often runs two queries:

SELECT *
FROM orders
WHERE status = 'active'
LIMIT 20 OFFSET 0;

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

With a window function, the query can return page rows and the total count together:

SELECT
    id,
    user_id,
    amount,
    status,
    COUNT(*) OVER() AS total_count
FROM orders
WHERE status = 'active'
LIMIT 20 OFFSET 0;

Each returned row carries the same total_count.

Use partitioned counts when each row should include its group size:

SELECT
    id,
    department,
    salary,
    COUNT(*) OVER (PARTITION BY department) AS department_count
FROM employees;

Window function support:

Window counts are exact. They are not a magic performance shortcut; the database still has to compute the qualifying result set. Use them to avoid duplicated query logic and extra round trips.

Part 5: Fast Row Count Estimates - No Full Table Scan

COUNT(*) is exact. On large MVCC databases, exact can be expensive.

PostgreSQL documentation is blunt: SELECT count(*) FROM sometable needs effort proportional to table size because PostgreSQL must scan the table or an index that covers all rows.

When sql count rows without full scan is acceptable, use catalog statistics. These are estimates, not guaranteed invoice-grade numbers.

PostgreSQL estimates

pg_class.reltuples stores the planner's estimated row count.

SELECT reltuples::BIGINT AS estimated_count
FROM pg_class
WHERE oid = 'public.orders'::regclass;

For operational dashboards:

SELECT
    relname AS table_name,
    n_live_tup AS estimated_rows,
    n_dead_tup AS dead_rows,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

PostgreSQL count rows estimates are freshest after ANALYZE or autovacuum analyze. After heavy writes, they can drift. Use exact COUNT(*) for billing, compliance, and correctness-sensitive workflows.

MySQL estimates

For mysql count rows estimates:

SELECT TABLE_ROWS AS estimated_count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'orders';

Or:

SHOW TABLE STATUS LIKE 'orders';

MySQL documents that TABLE_ROWS is exact for some engines but approximate for InnoDB and may vary by 40% to 50%. That sounds large, but it is still useful for capacity planning and "is this table around ten million or one billion rows?" questions.

For exact mysql count rows results, use COUNT(*). InnoDB can choose a small secondary index for COUNT(*), but it still must produce an exact count under transaction visibility rules.

SQL Server estimates

SQL Server exposes fast row metadata through catalog and dynamic management views.

SELECT SUM(rows) AS estimated_count
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.orders')
  AND index_id IN (0, 1);

Alternative:

SELECT SUM(row_count) AS estimated_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.orders')
  AND index_id IN (0, 1);

Microsoft documents these row values as approximate. In practice, they are commonly used for monitoring and table-size dashboards because they avoid scanning the user table.

SQLite estimates

SQLite does not maintain a general live table-row counter like MyISAM. If you need a fast estimate, run ANALYZE and inspect planner statistics:

ANALYZE;

SELECT stat
FROM sqlite_stat1
WHERE tbl = 'orders'
  AND idx IS NULL;

The stat value is a planner statistic string. The first integer is the approximate number of rows for the table or index entry. Treat it as an estimate.

Estimate comparison

This is the core sql count rows without full scan tradeoff: speed comes from accepting statistics instead of exact row visibility.

Part 6: Cross-Database Syntax Reference

This table is the safe starting point for cross-database count rows in sql code. If you control one database, use the native syntax that reads best for that platform.

Part 7: Counting Rows in ORMs

SQLAlchemy

Use SQLAlchemy to ask the database for a count. Do not load objects and count them in Python.

from sqlalchemy import func, select, text
from sqlalchemy.orm import Session

with Session(engine) as session:
    total = session.query(Order).count()
    active = session.query(Order).filter(Order.status == "active").count()
    unique_users = session.query(func.count(func.distinct(Order.user_id))).scalar()

Core style:

with engine.connect() as conn:
    result = conn.execute(select(func.count()).select_from(Order.__table__))
    total = result.scalar_one()

Direct SQL:

with engine.connect() as conn:
    total = conn.execute(text("SELECT COUNT(*) FROM orders")).scalar_one()

For sqlalchemy count work in new code, prefer explicit select(func.count()) queries because they make the generated SQL obvious.

Django ORM

Use .count():

from myapp.models import Order

total = Order.objects.count()
active = Order.objects.filter(status="active").count()

Distinct and grouped counts:

from django.db.models import Count

unique_users = Order.objects.aggregate(
    unique_users=Count("user_id", distinct=True)
)["unique_users"]

dept_counts = (
    Employee.objects
    .values("department")
    .annotate(count=Count("id"))
    .order_by("-count")
)

The django count rows rule is direct: use QuerySet.count() for a database count, and avoid len(queryset) on a fresh large query because it evaluates the queryset and materializes rows.

# Bad for large querysets
count = len(Order.objects.all())

# Good
count = Order.objects.count()

Prisma

Prisma has a dedicated count() API:

import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient()

const total = await prisma.order.count()

const active = await prisma.order.count({
  where: { status: "active" }
})

For grouped counts:

const deptCounts = await prisma.employee.groupBy({
  by: ["department"],
  _count: { id: true },
  orderBy: { _count: { id: "desc" } }
})

For exact COUNT(DISTINCT ...) behavior, raw SQL is often clearer than forcing an ORM workaround:

const rows = await prisma.$queryRaw`
  SELECT COUNT(DISTINCT user_id) AS unique_users
  FROM orders
`

If you also process exported rows in application code, see count rows with Python and count rows with Node.js.

Part 8: Optimizing COUNT Queries

Indexes

Without an index, this query may scan the whole table:

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

Add an index for common filters:

CREATE INDEX idx_orders_status ON orders(status);

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

An index does not make every COUNT(*) free, but it can reduce I/O dramatically when the filter is selective or when the database can answer from an index-only scan.

Storage engine behavior

MySQL MyISAM can maintain table row counts cheaply, which is why COUNT(*) on MyISAM can be extremely fast. InnoDB does not keep one exact global row count because concurrent transactions can see different snapshots.

PostgreSQL also has MVCC visibility rules, so exact COUNT(*) must account for row visibility. That is why postgresql count rows queries on huge tables often need either patience, an index-only path, or an estimate.

SQL Server can often use metadata for approximate counts and indexes for exact filtered counts, but exact filtered counts still require evaluating the predicate.

Cached count table

If exact counts are needed constantly, maintain a separate count table.

CREATE TABLE table_counts (
    table_name VARCHAR(64) PRIMARY KEY,
    row_count BIGINT NOT NULL DEFAULT 0
);

INSERT INTO table_counts(table_name, row_count)
VALUES ('orders', (SELECT COUNT(*) FROM orders));

MySQL trigger example:

DELIMITER //

CREATE TRIGGER orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE table_counts
    SET row_count = row_count + 1
    WHERE table_name = 'orders';
END//

CREATE TRIGGER orders_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE table_counts
    SET row_count = row_count - 1
    WHERE table_name = 'orders';
END//

DELIMITER ;

Query:

SELECT row_count
FROM table_counts
WHERE table_name = 'orders';

This is fast, but it moves complexity into writes. Use it only when the product truly needs exact, frequent, low-latency counts.

Exact Count or Estimate?

Do you need an exact row count?
|
+-- Yes
|   |
|   +-- Small or medium table -> COUNT(*)
|   +-- Large filtered count -> COUNT(*) plus the right index
|   +-- Huge table and frequent exact reads -> cached count table
|
+-- No
    |
    +-- PostgreSQL -> pg_class.reltuples or pg_stat_user_tables
    +-- MySQL -> information_schema.TABLES.TABLE_ROWS
    +-- SQL Server -> sys.partitions or dm_db_partition_stats
    +-- SQLite -> ANALYZE plus sqlite_stat1

The durable rule is simple: exact sql count rows results cost real work; estimates are fast because they reuse statistics.

FAQ

How do I count rows in SQL?

Use:

SELECT COUNT(*) FROM table_name;

That is the universal count rows in sql answer for exact total rows.

What is the difference between COUNT(*) and COUNT(1)?

COUNT(*) vs COUNT(1) is not a performance decision in modern systems. Both count rows. Use COUNT(*) because it is clearer and standard.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows. COUNT(column) counts only non-NULL values in that column.

How do I count rows with a condition in SQL?

Use:

SELECT COUNT(*) FROM orders WHERE status = 'completed';

That is the standard sql count rows where condition pattern.

How do I count rows after GROUP BY?

Use COUNT(*) with GROUP BY:

SELECT status, COUNT(*)
FROM orders
GROUP BY status;

How do I count distinct values in SQL?

Use COUNT(DISTINCT column). For multi-column COUNT DISTINCT sql, use PostgreSQL tuple syntax or a portable SELECT DISTINCT subquery.

How do I count rows without a full table scan?

Use catalog estimates. PostgreSQL has pg_class.reltuples, MySQL has information_schema.TABLES, SQL Server has partition metadata, and SQLite has sqlite_stat1 after ANALYZE.

How do I count rows in SQLAlchemy?

Use SQLAlchemy count expressions such as select(func.count()).select_from(table), or legacy ORM .count() when appropriate. Avoid loading all rows into Python.

How do I count rows in Django ORM?

Use QuerySet.count(). The django count rows mistake is using len(queryset) on a fresh large queryset, which fetches rows instead of asking the database for a count.

How do I count filtered rows in SQL?

Use COUNT(*) with WHERE, or use conditional aggregation if you need several filtered counts in one query.

Sources Checked

• PostgreSQL aggregate function documentation for count(*) behavior and scan cost: https://www.postgresql.org/docs/current/functions-aggregate.html
• PostgreSQL pg_class.reltuples catalog documentation: https://www.postgresql.org/docs/current/catalog-pg-class.html
• PostgreSQL aggregate expression FILTER syntax: https://www.postgresql.org/docs/current/sql-expressions.html
• MySQL aggregate function documentation for COUNT(*) and COUNT(1): https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html
• MySQL information_schema.TABLES documentation for TABLE_ROWS estimates: https://dev.mysql.com/doc/refman/8.4/en/information-schema-tables-table.html
• SQLite window functions and FILTER clause documentation: https://www.sqlite.org/windowfunctions.html
• SQLite ANALYZE and sqlite_stat1 documentation: https://www.sqlite.org/lang_analyze.html
• Microsoft COUNT Transact-SQL documentation: https://learn.microsoft.com/sql/t-sql/functions/count-transact-sql
• Microsoft sys.partitions documentation: https://learn.microsoft.com/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql
• SQLAlchemy query count documentation: https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html
• Django QuerySet count() documentation: https://docs.djangoproject.com/en/stable/ref/models/querysets/#count
• Prisma Client aggregation and count documentation: https://www.prisma.io/docs/orm/prisma-client/queries/aggregation-grouping-summarizing

Related Guides and Tools

• count rows with Python for CSV exports, scripts, and data files.
• count rows with Node.js for API exports and streaming files.
• count rows with Java JDBC for JVM services and large-file processing.
• C# line counting for .NET services, logs, and CSV exports.
• count rows in Excel for spreadsheets and CSV workflows.
• Line Counter tool for browser-based text and file counting.

Working with Exported SQL Data?

If you have a CSV export from your database and need to count the lines without running a query or connecting to a database, paste it into the Line Counter.

It works on any text file instantly in your browser.