Sql Excel : A Basic Summary SQL Query

A very powerful component of SQL is the ability to summarize data in a table. The
following SQL counts the number of zip codes in ZipCensus:
SELECT COUNT(*) as numzip
FROM ZipCensus zc
The form of this query is very similar to the basic select query. The function
COUNT(*), not surprisingly, counts the number of rows. The “*” means that all rows
are being counted. It is also possible to count a column, such as COUNT(zcta5). This
counts the number of rows that have a valid (i.e., non-NULL) value in zcta5.
The preceding query is an aggregation query that treats the entire table as a single
group. Within this group, the query counts the number of rows, which calculates
the number of rows in the table. A very similar query returns the number of zip
codes in each state:
SELECT stab, COUNT(*) as numzip
FROM ZipCensus zc
GROUP BY stab
ORDER BY numzip DESC

The GROUP BY clause says to treat the table as consisting of several groups defined
by the different values in the column stab. The result is then sorted in reverse
order of the count (DESC stands for “descending”), so the state with the most zip
codes (Texas) is first.

In addition to COUNT(), standard SQL offers other useful aggregation functions.
The SUM(), AVG(), MIN(), and MAX() functions compute, respectively, the sum,
average, minimum, and maximum values. In general, the first two operate only on
numeric values and the MIN() and MAX() can work on any data type. Note that all
these functions ignore NULL values in their calculations.
COUNT(DISTINCT) returns the number of distinct values. An example of using it is to
answer the following question: How many SCFs are in each state? The following
query answers this question:
SELECT zc.stab, COUNT(DISTINCT LEFT(zc.zcta5, 3)) as numscf
FROM ZipCensus zc
GROUP BY zc.stab
ORDER BY zc.stab
This query also shows that functions, such as LEFT(), can be nested in the
aggregation functions. SQL allows arbitrarily complicated expressions. Chapter 2
shows another way to answer this question using subqueries.