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 isnt meaningful with COUNT(*); see Aggregating Distinct Values with DISTINCT later in this chapter.