Page 503 - Microsoft Office Excel 2003 Programming Inside Out

Excel and the Structured Query Language

Resolving Column Names
If you tried to code the preceding example as a Select statement, you would run into a prob­
lem because both tables have a CustomerID column. Fortunately, the Select statement lets you
use dot notation to combine a table name with a column name to uniquely identify a column
when dealing with multiple tables. The form is <tablename>.<columnname>, so in the case
of the previous example, you would refer to the columns as Customers.CustomerId and
Orders.CustomerId. The resulting Select statement would look like this:

Select *
From Customers, Orders
Where Customers.CustomerId = Orders.CustomerId

Using Aliases
Just as Excel lets you create names you can use to refer to groups of cells, SQL lets you define
aliases for a table name. An alias is merely a short name that can be used in place of the table’s
name. To define an alias, you follow the name of the table with its alias when you refer to the
table in the From clause.

Select *
From Customers c, Orders o
Where c.CustomerId = o.CustomerId

Using Functions

The Select statement lets you perform summary operations over the entire set of rows that
would normally be returned. Just as summary operations in Excel (such as SUM, COUNT, or
COUNTBLANK) return a single value, the SQL summary functions return a single row con­ Chapter 22
taining the result of the function. For example, you can count the number of records a Select
statement might return by using the COUNT function to count the number of CustomerId
values retrieved.

Select Count(CustomerId)
From Customers
Other functions available include: MIN, MAX, and AVERAGE. You should be aware that
while the COUNT function merely counts each individual row, the other functions work
with the value contained in each individual column. Thus, MAX and MIN will return the
largest and smallest values found in that column, respectively. The AVERAGE function totals
all the values found in that column and then divides by the number of rows. If you apply the
AVERAGE function to a column that doesn’t contain numeric values, you will get an error.

477
Part 6: Excel and the Outside World: Collaborating Made Easy