SQL COUNT() Function: Syntax, Usage, and Examples

The COUNT() function returns the number of rows that match a query. It’s one of the most frequently used aggregate functions in SQL, and it plays a vital role in reporting, data summaries, and analytics.

How to Use COUNT in SQL

The basic syntax of COUNT can vary depending on what you're counting—rows, specific values, or distinct values. Here’s the general syntax:

SELECT COUNT(*) FROM table_name;

This counts all rows in the table, including those with NULL values.

COUNT(*) vs COUNT(column)

• COUNT(*): Counts all rows, regardless of null values.
• COUNT(column_name): Counts only non-null values in the specified column.

SELECT COUNT(email) FROM users;

This counts users who have a non-null email.

When to Use COUNT

The COUNT function is useful any time you need a tally:

• Count total records in a table
• Count non-null values in a specific column
• Count distinct values
• Count grouped data
• Validate results in subqueries or joins

SELECT COUNT for Quick Audits

Quickly check table size:

SELECT COUNT(*) FROM customers;

This is commonly used to verify data loads, troubleshoot queries, or monitor data growth.

Using COUNT in Filtering

You can filter results using HAVING with COUNT:

SELECT country, COUNT(*) AS num_customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 100;

This finds countries with more than 100 customers.

Examples of COUNT

Example 1: COUNT Total Rows

SELECT COUNT(*) AS total_orders
FROM orders;

Returns the total number of rows in the orders table.

Example 2: COUNT Non-Null Values

SELECT COUNT(email) AS valid_emails
FROM users;

Counts users with a valid (non-null) email.

Example 3: COUNT DISTINCT

SELECT COUNT(DISTINCT country) AS unique_countries
FROM customers;

This COUNT DISTINCT query tells you how many different countries your customers come from.

Example 4: Query Count by Group

SELECT status, COUNT(*) AS total
FROM support_tickets
GROUP BY status;

This breaks down ticket count by status—open, closed, pending, etc.

Example 5: COUNT of COUNT Pattern

In advanced reports, you might need a COUNT OF COUNT structure, using a subquery:

This counts how many departments have more than 50 employees.

Learn More About COUN

COUNT with WHERE Clause

You can combine COUNT with a WHERE clause to count rows matching specific conditions:

SELECT COUNT(*) AS late_orders
FROM orders
WHERE delivery_status = 'Late';

This counts only rows where the status is 'Late'.

COUNT in Joins

When using joins, be careful which table’s columns you count. For example:

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Using COUNT(o.id) avoids counting nulls when a customer has no orders.

COUNT UNIQUE Values

Another way to say sql count unique is to use COUNT(DISTINCT column):

SELECT COUNT(DISTINCT email) AS unique_emails
FROM newsletter_subscribers;

This avoids duplicate entries in the count.

Combining COUNT with Other Aggregates

You can use COUNT alongside SUM, AVG, or MAX:

SELECT department, COUNT(*) AS total_staff, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

This summarizes both headcount and average salary per department.

COUNT with CASE for Conditional Logic

You can use CASE statements to perform conditional counts:

SELECT
  COUNT(CASE WHEN status = 'Open' THEN 1 END) AS open_tickets,
  COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS closed_tickets
FROM support_tickets;

This gives you multiple counts in one query, broken down by condition.

SQL Query Count Across Date Ranges

Often, you’ll count values over time to analyze trends:

SELECT
  DATE(order_date) AS day,
  COUNT(*) AS daily_orders
FROM orders
GROUP BY DATE(order_date)
ORDER BY day;

This turns row-level data into meaningful insights for visualization or reporting.

Using COUNT in Subqueries

In real-world queries, COUNT often appears inside subqueries. For example:

SELECT name
FROM products
WHERE (
  SELECT COUNT(*)
  FROM order_items
  WHERE order_items.product_id = products.id
) > 100;

This finds products ordered more than 100 times.

COUNT in Common Table Expressions (CTEs)

CTEs make COUNT logic easier to read and extend:

WITH department_stats AS (
  SELECT department_id, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department_id
)
SELECT * FROM department_stats
WHERE employee_count > 20;

CTEs are useful for creating modular, readable SQL code.

COUNT and Performance

COUNT(*) can be fast or slow depending on the engine and table size. Indexes, partitions, and storage engines matter. Some databases like PostgreSQL or MySQL InnoDB perform a full scan for COUNT(*), while others like SQL Server can use metadata.

To improve performance:

• Use WHERE clauses to limit data scanned
• Avoid COUNT(*) on joined views unless necessary
• Use indexed columns in COUNT(column) when possible

Alternatives to COUNT

In some cases, developers mistakenly use SELECT * to check if a table has data. Use COUNT(*) or EXISTS instead:

-- Less efficient
SELECT * FROM products;

-- Better for checking presence
SELECT COUNT(*) FROM products;

-- Even faster if you just need existence
SELECT EXISTS(SELECT 1 FROM products);

EXISTS returns faster in large tables if only presence is needed.