Publishers of technology books, eBooks, and videos for creative people

# Summarizing and Grouping Data in SQL

• Print
This chapter is from the book

## Counting Rows with COUNT()

Use the aggregate function COUNT() to count the number of rows in a set of values. COUNT() has two forms:

• COUNT(expr) returns the number of rows in which expr is not null.

• COUNT(*) returns the count of all rows in a set, including nulls and duplicates.

#### To count non-null rows:

• Type:

`COUNT(expr)`

expr is a column name, literal, or expression. The result is an integer greater than or equal to zero.

#### To count all rows, including nulls:

• Type:

`COUNT(*)`

COUNT(*) returns an integer greater than or equal to zero.

Listing 6.5 and Figure 6.5 show some queries that involve COUNT(expr) and COUNT(*). The three queries count rows in the table titles and are identical except for the WHERE clause. The row counts in the first query differ because the column price contains a null. In the second query, the row counts are identical because the WHERE clause eliminates the row with the null price before the count. The third query shows the row-count differences between the results of first two queries.

#### Listing 6.5 Some COUNT() queries. See Figure 6.5 for the results.

```SELECT
COUNT(title_id) AS "COUNT(title_id)",
COUNT(price) AS "COUNT(price)",
COUNT(*) AS "COUNT(*)"
FROM titles;

SELECT
COUNT(title_id) AS "COUNT(title_id)",
COUNT(price) AS "COUNT(price)",
COUNT(*) AS "COUNT(*)"
FROM titles
WHERE price IS NOT NULL;

SELECT
COUNT(title_id) AS "COUNT(title_id)",
COUNT(price) AS "COUNT(price)",
COUNT(*) AS ""COUNT(*)"
FROM titles
WHERE price IS NULL;```

Figure 6.5 Results of Listing 6.5.

TIPS

• COUNT(expr) and COUNT(*) work with alldata types.

• COUNT(expr) and COUNT(*) neverreturn null.

• DISTINCT isn’t meaningful with COUNT(*); see “Aggregating Distinct Values with DISTINCT” later in this chapter.