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

Home > Articles

  • Print
  • + Share This
This chapter is from the book

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.5Figure 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.

  • + Share This
  • 🔖 Save To Your Account