| Name: | CROSSTAB | Shortest: | CRO |
|---|---|---|---|
| Family: | Command | Category: | Data Manipulation |
| Description: | Use the CROSSTAB command to count the occurrences of each unique pair of values from two columns in a table or view. | ||
AVG
Computes the numeric average of CURRENCY, DOUBLE, NUMERIC, REAL, INTEGER, DATE, or TIME data types. R:BASE rounds averages of integer values to the nearest integer value and currency values to their nearest unit.
BY sidecol
Specifies the column whose values are displayed down the side of the crosstab.
cellcol
Specifies the column whose values are computed and displayed in the cells portion of the crosstab.
COUNT
Determines how many non-null entries there are for a particular column item.
FOR topcol
Specifies the column whose values are displayed across the top of the crosstab. If the topcol option is omitted, the sidecol and cellcol total or computation is displayed.
FROM tblview
Specifies the table or view.
MAXIMUM
Selects the maximum value in a column. For TEXT and CHARACTER data types, R:BASE evaluates the first eight characters. This option accepts columns with any data type except BIT, LONG VARBIT, LONG VARCHAR, VARBIT, VARCHAR, and BITNOTE.
MINIMUM
Selects the minimum value in a column. For TEXT and CHARACTER data types, R:BASE evaluates the first eight characters. This option accepts columns with any data type except BIT, LONG VARBIT, LONG VARCHAR, VARBIT, VARCHAR, and BITNOTE.
SUM
Computes the numeric sum of CURRENCY, DOUBLE, REAL, NUMERIC, or INTEGER data types.
topcol
Specifies the column whose values are displayed across the top of the crosstab.
=w
Sets the width, in number of characters, of the column displaying the results for cellcol.
WHERE clause
Limits rows of data. For more information, see the "WHERE" entry.
You can use CROSSTAB from the R> prompt from the Calculatemenu in the "Data Browser" window in R:BASE for Windows or Calculate from the Info/views menu in R:BASE for DOS. R:BASE sorts the information in ascending order and displays it in tabular form. You can also display the results of numeric calculations performed on the values in the two columns in a third column.
You can now do CROSSTAB commands on NOTE fields. It will only use the first 20 characters, but this permits text expressions from views to be used.
The CROSSTAB command does not consider null values in its calculations.
| Argument | Description |
|---|---|
| AVG | Computes the numeric average of CURRENCY, DOUBLE, NUMERIC, REAL, INTEGER, DATE, or TIME data types. R:BASE rounds averages of integer values to the nearest integer value and currency values to their nearest unit. |
| COUNT | Determines how many entries there are for a particular column item. |
| MAXIMUM | Selects the value in a column with the greatest numeric, time, date, or alphabetic value. For a TEXT data type, R:BASE evaluates the first eight characters. |
| MINIMUM | Selects the value in a column with the least numeric, time, date, or alphabetic value. For a TEXT data type, R:BASE evaluates the first eight characters. |
| SUM | Computes the numeric sum of CURRENCY, DOUBLE, REAL, NUMERIC, INTEGER, or data types. |
The following command shows the net sum of the transactions for the sales representative whose employee identification is 102, ordered by transaction date.
CROSSTAB SUM netamount FOR empid BY transdate FROM + transmaster WHERE empid = 102
The results look similar to the following:
transdat | 102 (Total)
-------- | --------------- ---------------
01/11/94 | $176,000.00 $176,000.00
02/26/94 | $87,500.00 $87,500.00
02/27/94 | $22,500.00 $22,500.00
02/28/94 | $40,500.00 $40,500.00
03/07/94 | $57,500.00 $57,500.00
09/26/94 | $3,060.00 $3,060.00
-------- | --------------- ---------------
| $387,060.00 $387,060.00