SQL Count() Function

The SQL COUNT() function is used to return the number of rows or non-null values in a column. It helps summarize data in reports and analytics.

• Counts all rows or rows with a condition
• Counts unique values using COUNT(DISTINCT column)
• Commonly used with GROUP BY and HAVING for grouped results

Example: First, we create a demo SQL database and table, on which we will use the COUNT() functions.

Query:

SELECT COUNT(*) AS TotalEmployees FROM Employee;
SELECT COUNT(DISTINCT Department) AS UniqueDepartments
FROM Employee;

Output:

• First query returns total number of employees.
• Second query returns count of unique departments.

Syntax:

COUNT(expression)

1. Count all rows:

SELECT COUNT(*) FROM table_name;

2. Count distinct values in a column:

SELECT COUNT(DISTINCT column_name) FROM table_name;

Examples of SQL Count Function

Let’s explore practical examples of the COUNT() function using a sample Customers table; consider the table below for all examples.

1. Counting the Number of Rows with COUNT(*)

When we want to count all the rows in a table, regardless of the column values, we can use COUNT(*). It counts every row, including rows with NULL values.

Query:

SELECT COUNT(*) AS TotalCustomers
FROM Customers;

Output:

• This query counts all the rows in the Customers table, including those with NULL values in any column.
• The result 9 indicates that there are 9 rows in the Customers table.

2. Counting Unique Values with COUNT(DISTINCT …)

Sometimes, we may need to count only the distinct values in a column. The COUNT(DISTINCT column_name) function allows us to count only unique entries in a column, ignoring duplicates.

Query:

SELECT COUNT(DISTINCT Country) FROM Customers;

Output:

• This query counts the number of unique countries in the Customers table.
• The result 4 indicates that there are four distinct countries listed in the table (Spain, Mexico, India, and Germany).

3. Count Rows That Match a Condition Using COUNT() with CASE WHEN

We can use the COUNT() function along with CASE WHEN to count rows that match a specific condition. This is helpful when we want to count rows based on certain criteria without filtering the rows out of the result set.

Query:

SELECT COUNT(CASE WHEN Age > 30 THEN 1 ELSE NULL END) AS Adults 
FROM Customers;

Output:

• This query counts the number of customers whose age is greater than 30.
• The CASE WHEN expression checks if the condition Age > 30 is met. If it is, the value 1 is returned (otherwise, NULL is returned).
• The COUNT() function then counts the non-NULL values, giving us the total number of customers over 30 years old.
• The result 5 shows that five customers are older than 30 years.

4. Count Rows in Groups Using COUNT() with GROUP BY

We can use the COUNT() function with GROUP BY to count rows within groups based on a column. This is useful when we want to categorize data and then count how many records exist in each category.

Query:

SELECT Country, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country;

Output:

• This query groups the rows in the Customers table by the Country column and then counts how many customers belong to each country.
• The result shows the number of customers from each country: 1 from Germany, , 2 from India, 2 from Mexico, and 4 from Spain

5. Filter Groups Using COUNT() with GROUP BY and HAVING

We can combine the COUNT() function with HAVING to filter the results after grouping. The HAVING clause is used to specify conditions on groups, similar to the WHERE clause, but for groups.

Query:

SELECT Country, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country
HAVING COUNT(*) > 2;

Output:

• This query counts the number of customers from each country and filters out countries with fewer than 3 customers.
• The result only includes Spain, as it is the only country with more than 2 customers.

Best Practices for Using the COUNT() Function

The COUNT() function is powerful and widely used, but to keep queries efficient and easy to maintain, it's important to follow a few best practices.

1. Optimize Queries for Large Datasets:

On large tables, COUNT() can be slow if the database must scan the entire dataset. Adding indexes to frequently counted columns improves performance.

Query:

-- Create index on Country column
CREATE INDEX idx_country 
ON Customers(Country);

-- Faster COUNT query using the index
SELECT COUNT(*)
FROM Customers
WHERE Country = 'Spain';

Output:

• The index on the Country column helps the database quickly find matching rows instead of scanning the entire table.
• The COUNT(*) query then runs faster when counting customers from Spain.

2. Avoid Complex COUNT Queries for Large Tables:

Queries with many conditions or subqueries can be slow on very large tables. Simplify or break them into smaller parts to improve performance.

Query:

SELECT COUNT(*)
FROM Customers
WHERE (Country = 'Spain' OR Country = 'France') 
AND Age > 30 
AND City = 'Barcelona';

Output:

• Counts customers who are from Spain or France, are older than 30, and live in Barcelona.
• Such complex COUNT queries may be slow on large tables, and performance can improve by breaking the logic into smaller, simpler queries.