How to use SQL COUNT() to count rows in a table

You can use the SQL COUNT() function to query the number of rows within a table. Adding a WHERE condition allows you to further specify the search and output criteria.

What is SQL COUNT()?

Within Structured Query Language there are numerous functions you can use to perform simple mathematical tasks. In addition to SQL AVG() and SUM(), SQL COUNT() is useful. The function gives you the number of rows that match a certain criterion, allowing you to filter your table according to your own requirements, thus increasing clarity. SQL COUNT() is used with the SQL command SELECT. You can use various optional parameters to instruct the function even more precisely. Some possible examples are shown below.

Syntax of the function

Generally, it’s worth taking a look at the syntax and functionality of SQL COUNT() first. The standard version is only two lines long and looks like this:

SELECT COUNT(*)
FROM table;

However, often it’s useful to include a WHERE condition to specify the output. In this case, the function becomes a little longer:

SELECT COUNT(*)
FROM table 
WHERE condition;

Example of the functionality

To illustrate the possibilities offered by SQL COUNT(), let’s create an example table called Customer List. This table contains information on the customer number, the name of the customer, their location, the number of items ordered, and the amount invested in dollars:

If we now apply the basic variant of the function to this table, the following code results:

SELECT COUNT(*)
FROM CustomerList;

We receive the value 5 as output, as our table contains five rows.

Specify query of the columns

Use the asterisk after COUNT to instruct the system to include all rows and fields without exception. However, it’s possible to reduce the query to certain columns only. In this case, NULL values aren’t taken into account. So, if we check the number of rows in Location, we’ll get a different overall result:

SELECT COUNT(Location) 
FROM CustomerList;

As no location has been entered for customer Davis, we get the result 4.

Combination with WHERE

In the next step, we use the function with a WHERE condition. This helps us filter the entries according to our criteria. For example, if we want to check how many customers come from Chicago, we add this condition:

SELECT COUNT(Location)
FROM CustomerList
WHERE Location = 'Chicago';

The result is now 2.

SQL COUNT() works in a similar way if we only want to consider customers who have made a purchase of at least 400 dollars:

SELECT COUNT(Location) 
FROM CustomerList 
WHERE Total > 400;

This applies to three customers.

Exclude duplicate entries

Using the keyword DISTINCT let’s you exclude duplicate entries. To find out how many different cities your customers come from, use the following code:

SELECT COUNT(DISTINCT Location) 
FROM CustomerList;

The result in this case is 3. The two entries for Chicago were only counted once, and since the field for customer Davis is NULL, it is not counted.

The function with an alias

If you also want to title the output, you can use the keyword “AS” (more on SQL AS in our guide). This creates an alias for the duration of the query:

SELECT COUNT(*) AS [number of entries] 
FROM CustomerList;

The output reads as follows:

Number of entries
5