- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
SQL COUNT() Function: Syntax, Usage, and Examples
The SQL 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 SQL
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 SQL COUNT
The sql 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
SQL 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 SQL COUNT in Filtering
You can filter results using HAVING
with COUNT SQL
:
SELECT country, COUNT(*) AS num_customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 100;
This finds countries with more than 100 customers.
Examples of SQL 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: SQL COUNT DISTINCT
SELECT COUNT(DISTINCT country) AS unique_countries
FROM customers;
This sql count distinct
query tells you how many different countries your customers come from.
Example 4: SQL 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 SQL Pattern
In advanced reports, you might need a count of count sql
structure, using a subquery:
This counts how many departments have more than 50 employees.
Learn More About COUNT SQL
COUNT with WHERE Clause
You can combine SQL 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.
SQL 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 sql
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.
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.