SQL enables you to collect rows into groups and to summarize those groups in various ways, ultimately, returning just one row per group. You do this using the GROUP BY and HAVING clauses, as well as various aggregate functions.
An aggregate function takes a group of values, one from each row in a group of rows, and returns one value as output. One of the most common aggregate functions is COUNT, which counts non-null values in a column. For example, to count the number of waterfalls associated with a county, specify:
SELECT COUNT(u.county_id) AS county_count FROM upfall u;14
Add DISTINCT to the preceding query to count the number of counties containing waterfalls:
SELECT COUNT(DISTINCT u.county_id) AS county_count FROM upfall u;6
The ALL behavior is the default, counting all values:
) is equivalent to
COUNT is a special case of aggregate functions because you can pass the asterisk (*) to count rows rather than column values:
SELECT COUNT(*) FROM upfall;
Nullity is irrelevant when
COUNT(*) is used because the concept of null applies only to columns, not to entire rows as a whole. All other aggregate functions ignore nulls.
Table 11 lists some commonly available aggregate functions. However, most database vendors implement aggregate functions beyond those shown. Check your documentation if you need an aggregation beyond those listed in Table 11.
Table 11. Common aggregate functions