Crosstabulations

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:

Restricting pivot field values

Other types of SQL statements