COUNT

Overview

The COUNT() function is an aggregate function that returns the number of rows that match a specified condition. It's one of the most commonly used aggregate functions in SQL.

COUNT() is useful for counting records, determining table sizes, and creating summary statistics.

Basic Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

COUNT(*) - Count All Rows

The most common use is COUNT(*) which counts all rows in a table:

SELECT COUNT(*) AS total_customers
FROM Customers;

COUNT(column_name) - Count Non-NULL Values

COUNT(column_name) counts only non-NULL values in the specified column:

-- Count customers with email addresses
SELECT COUNT(email) AS customers_with_email
FROM Customers;

Difference Between COUNT(*) and COUNT(column)

Understanding the difference:

Examples

Example 1: Count All Records

SELECT COUNT(*) AS total_products
FROM Products;

Example 2: Count with WHERE

SELECT COUNT(*) AS active_customers
FROM Customers
WHERE status = 'Active';

Example 3: COUNT DISTINCT

-- Count unique cities
SELECT COUNT(DISTINCT city) AS unique_cities
FROM Customers;

Example 4: Count with Multiple Conditions

SELECT COUNT(*) AS high_value_orders
FROM Orders
WHERE total_amount > 1000
  AND order_date >= '2024-01-01';

COUNT with GROUP BY

Use COUNT() with GROUP BY to count records per group:

SELECT 
    category,
    COUNT(*) AS product_count
FROM Products
GROUP BY category;

Example Result:

┌──────────────┬───────────────┐
│ category     │ product_count │
├──────────────┼───────────────┤
│ Electronics  │ 25            │
│ Clothing     │ 30            │
│ Books        │ 15            │
└──────────────┴───────────────┘

COUNT with HAVING

Filter groups using HAVING after GROUP BY:

SELECT 
    category,
    COUNT(*) AS product_count
FROM Products
GROUP BY category
HAVING COUNT(*) > 20;

Multiple COUNT Operations

You can use multiple COUNT operations in a single query:

SELECT 
    COUNT(*) AS total_customers,
    COUNT(email) AS customers_with_email,
    COUNT(DISTINCT city) AS unique_cities
FROM Customers;

COUNT with JOIN

Count records from joined tables:

SELECT 
    c.name AS customer_name,
    COUNT(o.order_id) AS order_count
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

COUNT NULL Values

To count NULL values, use a conditional expression:

-- Count customers without email
SELECT COUNT(*) - COUNT(email) AS customers_without_email
FROM Customers;

-- Or use CASE
SELECT 
    COUNT(CASE WHEN email IS NULL THEN 1 END) AS customers_without_email
FROM Customers;

Common Use Cases

• Counting total records in a table
• Counting records matching specific criteria
• Finding number of unique values
• Creating summary statistics
• Checking data quality (counting NULLs)

Performance Tips

• COUNT(*) is usually fastest: Most databases optimize COUNT(*)
• Use indexes: Indexes on columns in WHERE clauses improve COUNT performance
• Avoid counting large tables: Consider approximate counts for very large tables
• Use WHERE first: Filter before counting to reduce rows processed

Best Practices

• Use COUNT(*) for total rows: It's the most efficient
• Use COUNT(column) for non-NULL counts: When you need to exclude NULLs
• Use COUNT(DISTINCT) for unique counts: When duplicates matter
• Give meaningful aliases: Name COUNT results descriptively
• Combine with other aggregates: Use with SUM, AVG, MIN, MAX for comprehensive analysis

Next Steps

Learn about calculating sums and averages with SUM and AVG, or explore grouping data with GROUP BY.