VMware ESXi and vSphere Cluster Management

SQL COUNT() Function: Counting Rows and Non-NULL Values

Learn how SQL COUNT(), COUNT(*), WHERE, and column aliases count rows and non-NULL values in query results.

What does the SQL COUNT() function do?

COUNT() is an SQL aggregate function. An aggregate function summarizes values from multiple rows and returns one result. COUNT() returns a numeric total representing rows or non-NULL values selected by a query.

Common uses include determining how many records a table contains and counting records that satisfy a condition, such as the number of cities in a particular country.

A result set is the rows and columns returned by a query. When you use COUNT() without GROUP BY, the result set normally contains one summary row, even when the query examines many source rows.

COUNT(*) counts every returned row

The expression COUNT(*) counts every row returned by the query. It does not inspect whether individual column values are NULL. If a row is part of the result set, it is included.

SELECT COUNT(*)
FROM table_name;

For example, this query counts all records in a products table:

SELECT COUNT(*) AS product_count
FROM products;

The query returns one numeric value: the number of rows in products. Rows are included even if some product columns contain missing values.

Using COUNT(*) with WHERE

A WHERE clause filters rows before the aggregate function calculates its result. Therefore, COUNT(*) counts only rows that satisfy the condition.

SELECT COUNT(*) AS active_product_count
FROM products
WHERE status = 'active';

This counts active products, not every product in the table. The database first selects rows whose status is 'active', then counts those rows.

COUNT(column_name) counts non-NULL values

COUNT(column_name) counts only rows where the named column has a non-NULL value.

SELECT COUNT(column_name)
FROM table_name;

NULL is a marker for missing or unknown data. It is not the same as zero, an empty string, or the text 'NULL'. When you count a column, rows in which that column is NULL are excluded.

For example, this query counts city rows with a populated CountryCode:

SELECT COUNT(CountryCode) AS country_code_count
FROM city;

The result is the number of city rows whose CountryCode is not NULL. It may be smaller than COUNT(*) if some cities have no country code.

COUNT(*) versus COUNT(column_name)

Consider a customers table containing five rows. If two customers have NULL in email, these expressions produce different results:

SELECT
  COUNT(*) AS all_customer_rows,
  COUNT(email) AS rows_with_email
FROM customers;

A representative result is:

all_customer_rows | rows_with_email
------------------+----------------
5                 | 3

COUNT(*) includes all five customer rows. COUNT(email) includes only the three rows with a non-NULL email address.

Filtering counts with WHERE

The WHERE clause chooses qualifying rows before COUNT() performs its calculation. This order matters:

1. The query reads rows from the table.
2. WHERE removes rows that do not match the condition.
3. COUNT() counts the remaining rows or non-NULL column values.

To count city records whose country code is USA, use a predicate on CountryCode:

SELECT COUNT(CountryCode) AS SameCountryCode
FROM city
WHERE CountryCode = 'USA';

This query first keeps rows where CountryCode equals 'USA'. It then counts the non-NULL CountryCode values among those rows.

Because the condition CountryCode = 'USA' normally matches only rows where CountryCode has that non-NULL value, the following two queries commonly return the same number:

SELECT COUNT(CountryCode) AS matching_country_count
FROM city
WHERE CountryCode = 'USA';

SELECT COUNT(*) AS matching_city_count
FROM city
WHERE CountryCode = 'USA';

That equality depends on the data and condition. For the exact equality predicate shown, a NULL value does not satisfy CountryCode = 'USA'. With a broader or different filter, the filtered rows could include NULL country codes, causing the two counts to differ.

Naming COUNT() results with aliases

A column alias is a temporary output name assigned to a selected expression. Use AS to give a count a meaningful name:

SELECT COUNT(*) AS product_count
FROM products;

Without an alias, a database may display a generated heading such as COUNT(*). An alias such as product_count, SameCountryCode, or matching_country_count makes result sets easier to read and use in applications.

Reading COUNT() results

When there is no GROUP BY clause, a count produces one summary value. Representative result sets might look like these:

product_count
-------------
248

SameCountryCode
---------------
37

The numbers are examples only. Exact output depends on the contents of the database. If no rows satisfy a WHERE condition, a query with COUNT() normally returns one row containing 0.

COUNT(column_name) is not a distinct-value count

COUNT(column_name) counts non-NULL occurrences, including repeated values. It does not count how many different values appear.

For example, if the non-NULL values in a column are USA, USA, and CAN, then COUNT(CountryCode) returns 3, not 2.

To count unique non-NULL values, use the separate DISTINCT option:

SELECT COUNT(DISTINCT CountryCode) AS distinct_country_count
FROM city;

This related form counts each distinct non-NULL country code once.

Common COUNT() syntax patterns

• SELECT COUNT(*) FROM table_name; counts all rows returned from a table.
• SELECT COUNT(column_name) FROM table_name; counts non-NULL values in one column.
• SELECT COUNT(column_name) AS result_name FROM table_name WHERE condition; filters rows, counts non-NULL values, and assigns a readable output name.

Troubleshooting COUNT() queries

COUNT(column_name) is smaller than expected

The counted column probably contains NULL values. Use COUNT(*) when the goal is to count every qualifying row, or investigate missing values in the selected column.

A filtered count is zero

No rows may satisfy the WHERE condition, or the comparison value may not match the stored data. Check spelling, data type, capitalization behavior in your database, and the values that are actually present.

COUNT(CountryCode) and COUNT(*) differ with a country filter

The condition may be broader than the equality example and may allow rows where CountryCode is NULL. Compare both expressions in one query:

SELECT
  COUNT(*) AS filtered_rows,
  COUNT(CountryCode) AS non_null_country_codes
FROM city
WHERE some_condition;

You can specifically inspect missing country codes with:

SELECT COUNT(*) AS missing_country_codes
FROM city
WHERE CountryCode IS NULL;

The output column name is unclear

Add a column alias with AS, such as AS product_count or AS matching_country_count.

The query is expected to count unique values

Repeated non-NULL values are included by ordinary COUNT(column_name). Use COUNT(DISTINCT column_name) when uniqueness is required.

Key points to remember

• COUNT() is an aggregate function that returns a numeric summary.
• COUNT(*) counts every row in the query result, including rows with NULL values in other columns.
• COUNT(column_name) excludes rows where that column is NULL.
• WHERE filters rows before the count is calculated.
• Use AS to give the output column a descriptive alias.
• Without GROUP BY, a count normally returns one summary row.
• Ordinary COUNT(column_name) includes repeated values; use DISTINCT for unique-value counts.

For a concise reference, see SQL COUNT() Function.