Crosstabulations (or contingency tables, or crosstabs for short), allow you to compare the entries in one field with those of another. For example, suppose we have three employees John, Richard and Susan, who make sales in two regions, north and south. The sales are recorded in a table, EmployeeSales. Such a table may look like this:
SaleID |
Employee |
Region |
1 |
John |
North |
2 |
John |
North |
3 |
Susan |
South |
4 |
Richard |
North |
5 |
John |
South |
6 |
Richard |
North |
7 |
Richard |
North |
8 |
John |
South |
9 |
Susan |
South |
Suppose you want a breakdown of sales by region, for each salesman. This can be done using a TRANSFORM statement:
TRANSFORM COUNT(Region) SELECT Employee FROM EmployeeSales GROUP BY Employee PIVOT Region;
This produces the following results table:
Employee |
North |
South |
John |
2 |
2 |
Richard |
3 |
|
Susan |
|
2 |
For each employee, at total of sales in each region is calculated.
Let’s look at the general form of this SQL statement. Given that you want to compare field_a against field_b, the SQL is really a standard GROUP select, wrapped in a TRANSFORM and PIVOT:
TRANSFORM COUNT(field_a) SELECT field_b FROM table GROUP BY field_b PIVOT field_a;
Suppose we wanted row totals. All we need to do is add a COUNT within the SELECT statement:
TRANSFORM COUNT(Region) SELECT Employee, COUNT(Employee) AS [Employee Total] FROM EmployeeSales GROUP BY Employee PIVOT Region;
producing the following table:
Emloyee |
Employee Total |
North |
South |
John |
4 |
2 |
2 |
Richard |
3 |
3 |
|
Susan |
2 |
|
2 |
Note that the employee count is given a new column title on the fly using the AS keyword.
As well as counting fields, other statistics can be used. Here’s a complete list:
Aggregate Function |
FIRST |
LAST |
COUNT |
MAX |
MIN |
See also: