Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 67

This result indicates that there are seven rows in the ORDER_ITEM table. Notice that we need
to provide an asterisk (*) after the COUNT function when we want to count rows. COUNT is
the only built-in function that requires a parameter, which can be the asterisk (as used in SQL-
Query-CH02-30) or a column name (as used in SQL-Query-CH02-31 that follows). The COUNT
function is also unique because it can be used on any type of data, but the SUM, AVG, MIN,
and MAX functions can only be used with numeric data.
The COUNT function can produce some surprising results. For example, suppose you want
to count the number of departments in the SKU_DATA table. If we use the following query:

/* *** SQL-Query-CH02-31 *** */
SELECT COUNT(Department) AS DeptCount

FROM SKU_DATA;

The result is:

which is the number of rows in the SKU_DATA table, not the number of unique values of
Department, as shown in Figure 2-5. If we want to count the unique values of Department, we
need to use the SQL DISTINCT keyword, as follows:

/* *** SQL-Query-CH02-32 *** */
SELECT COUNT(DISTINCT Department) AS DeptCount
FROM SKU_DATA;
The result of this query is:

Does Not Work with
Microsoft access
aNSI-89 SQL

Microsoft Access does not support the
DISTINCT keyword as part of the COUNT ex-
pression, so although the SQL command with
COUNT(Department) will work, the SQL command
with COUNT(DISTINCT Department) will fail.

Solution: Use an SQL subquery structure (discussed later in this chapter) with the
DISTINCT keyword in the subquery itself. This SQL query works:

/* *** SQL-Query-CH02-32-Access *** */
SELECT COUNT(*) AS DeptCount

FROM (SELECT DISTINCT Department

FROM SKU_DATA) AS DEPT;

Note that this query is a bit different from the other SQL queries using subqueries we
show in this text because this subquery is in the FROM clause instead of (as you'll see) the
WHERE clause. Basically, this subquery builds a new temporary table named DEPT con-
taining only distinct Department values, and the query counts the number of those values.

You should be aware of two limitations to SQL built-in functions. First, except for group-
ing (defined later), you cannot combine a table column name with an SQL built-in function.
For example, what happens if we run the following SQL query?

x

/* *** SQL-Query-CH02-33 *** */
SELECT Department, COUNT(*)

FROM SKU_DATA;