# 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](https://www.ionos.com/digitalguide/server/configuration/sql-introduction-with-examples/) there are numerous functions you can use to perform simple mathematical tasks. In addition to [SQL AVG()](https://www.ionos.com/digitalguide/server/configuration/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](https://www.ionos.com/digitalguide/server/configuration/sql-commands/) 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:

```sql
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:

```sql
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:

<table>
  <thead>
    <tr>
      <th><strong>Customer Number</strong></th>
      <th><strong>Name</strong></th>
      <th><strong>Location</strong></th>
      <th><strong>Items Ordered</strong></th>
      <th><strong>Total</strong></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1427</td>
      <td>Smith</td>
      <td>New York</td>
      <td>14</td>
      <td>634</td>
    </tr>
    <tr>
      <td>1377</td>
      <td>Brown</td>
      <td>Chicago</td>
      <td>9</td>
      <td>220</td>
    </tr>
    <tr>
      <td>1212</td>
      <td>Johnson</td>
      <td>Chicago</td>
      <td>15</td>
      <td>619</td>
    </tr>
    <tr>
      <td>1431</td>
      <td>Miller</td>
      <td>Los Angeles</td>
      <td>22</td>
      <td>912</td>
    </tr>
    <tr>
      <td>1118</td>
      <td>Davis</td>
      <td>NULL</td>
      <td>10</td>
      <td>312</td>
    </tr>
  </tbody>
</table>

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

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://www.ionos.com/digitalguide/server/configuration/sql-aliases/) in our guide). This **creates an alias for the duration of the query**:

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

The output reads as follows:

```sql
Number of entries
5
```

Tip Choose the database model that suits you: With [SQL Server Hosting](https://www.ionos.com/cloud/sql-server-hosting "SQL Server Hosting from IONOS") from IONOS, you can use MSSQL, MySQL, or MariaDB as you prefer, and benefit from top performance, robust security architecture, and personalized advice.


This is a markdown version of: [https://www.ionos.com/digitalguide/server/configuration/sql-count/](https://www.ionos.com/digitalguide/server/configuration/sql-count/) for AI/LLM consumption.