This clause, determines which columns to include.
Options
*
Specifies all rows, without regard to the values of the columns in any of the rows. From a performance standpoint, using "COUNT(colname)" would be slightly slower because in addition to fetching each row, it has to look at the column value in that row.
AVG
Computes the numeric average. R:BASE rounds averages of INTEGER values to the nearest integer value and CURRENCY values to their nearest unit.
COUNT
Determines how many non-null entries there are for a particular column item. The returned count value could be smaller than the table row count if there are null values.
DISTINCT
Eliminates duplicate rows from the calculation. The DISTINCT keyword is only needed to be specified for the first column in order to be applied to all columns in a query.
LISTOF
Creates a text string of the values separated by the current comma delimiter character. The text string is limited to 30,000 characters. As LISTOF is an aggregate-style function, any sorting (ORDER BY) needed for the listed results must be performed at a lower level, in which a view may be created to perform the sorting first.
The LISTOF function can be used with the "SELECT ... INTO ..." to populate a variable with a list of values which can then be used in a CHOOSE command with the #LIST option. It can also be used in forms, reports, or labels to look up values from multiple rows.
MAX
Selects the maximum numeric, time, date, or alphabetic value in a column.
MIN
Selects the minimum numeric, time, date, or alphabetic value in a column.
PSTDEV
Calculates population standard deviation.
PVARIANCE
Determines population variance.
SUM
Computes the numeric sum.
About SELECT Functions
A SELECT function can be used to provide summary data about a group of rows in a table or for all rows in a table. These functions may only be used with the GROUP BY clause or when only SELECT functions are specified.
Using MIN and MAX functions for NOTE data type
Selecting aggregate functions, such as MIN and MAX, requires that R:BASE keeps an accumulator and choose to only use the first 2,000 characters for NOTE/WIDENOTE values. If you sort on NOTE/WIDENOTE fields, the sort will be based on the first 80 characters only.
Examples
The COUNT function works in two different ways, depending on its argument. COUNT(*) counts all rows in a table, but (COUNT(colname) counts only rows in which the value in the specified column is not null. For example:
SELECT emptitle, COUNT(*), COUNT(emptitle) +
FROM employee GROUP BY emptitle
The final result contains both forms of the COUNT function.
emptitle COUNT(*) COUNT(emptitle)
Manager 2 2
Representative 3 3
Sales Clerk 1 1
-0- 2 0
If you wanted to compute the difference between each employee's average sales and the average sales for all employees, you would first use a SELECT function to calculate the average for all employees and store the result in a variable. For more information, see INTO.
The following command assigns the value $71,571.88 to the vaverage variable.
SELECT AVG(netamount) INTO vaverage FROM transmaster
Next, you can use the variable and another SELECT function in an expression to calculate the difference for each employee, and display the average net amount for each employee.
SELECT empid, AVG(netamount), +
(.vaverage - (AVG(netamount)))=30 +
FROM transmaster GROUP BY empid
empid AVG (netamount) (.vaverage - AVG(netamount))
102 $64,510.00 $7,061.88
129 $69,555.00 $2,016.88
131 $118,000.00 -$46,428.12
133 $44,380.00 $27,191.88
160 $114,850.00 -$43,278.12
165 $14,685.00 $56,886.88
167 $3,830.00 $67,741.88
Examples Using the LISTOF Function
SELECT (LISTOF(ColumnName)) INTO VariableName INDICATOR IndVar +
FROM TableView WHERE ...
In a Form, Report or Label Expression:
VariableName = (LISTOF(ColumnName)) IN LookUpTableView WHERE +
KeyColumn = KeyColumn
Example 01:
SET VAR vValueList TEXT = NULL
SELECT (LISTOF(ColumnName)) INTO vValueList INDIC IvValueList +
FROM TableName WHERE ...
The variable vValueList will be a text string of the values separated by the current comma delimiter character.
If you would like to add a single space after each value, then:
SET VAR vValueList TEXT = NULL
SELECT (SRPL(LISTOF(ColumnName),',',', ',0)) INTO +
vValueList INDIC IvValueList FROM TableName WHERE ...
Notice the additional space after the comma in SRPL function's replace string value.
If you would like to use a carriage return after each value, then:
SET VAR vValueList TEXT = NULL
SELECT (SRPL(LISTOF(ColumnName),',',(CHAR(10)),0)) INTO +
vValueList INDIC IvValueList FROM TableName WHERE ...
Example 02:
CONNECT Concomp IDENTIFIED BY NONE
SET CAPTION ' '
SET AUTODROP OFF
SET RBGSIZE CENTER CENTER 800 600
SET VAR vLines INTEGER = 0
SET VAR vValueList TEXT = NULL
SET VAR vLastName TEXT = NULL
SET VAR vTitle TEXT = 'List Created Using LISTOF Function'
SET VAR vCaption TEXT = 'Using #LIST Options in CHOOSE Command!'
CLS
PAUSE 3 USING 'Collecting Values ...' CAPTION .vCaption AT 16 30
SELECT (COUNT(*)), (LISTOF(EmpLName)) INTO +
vLines INDIC IvLines, vValueList INDIC IvValueList FROM Employee
IF vLines > 18 THEN
SET VAR vLines = 18
ENDIF
CLS
CHOOSE vLastname FROM #LIST .vValueList AT 6 30 +
TITLE .vTitle CAPTION .vCaption LINES .vLines FORMATTED
IF vLastName IS NULL OR vLastName = '[Esc]' THEN
GOTO Done
ELSE
CLEAR ALL VAR EXCEPT vLastName
ENDIF
-- Do what you have to do here ...
LABEL Done
CLS
CLEAR ALL VAR
QUIT TO MainMenu.RMD
RETURN
Example 03:
The following example retrieves a list of DISTINCT values from the InvoiceHeader table.
SET VAR vValueList TEXT = NULL
SELECT (LISTOF(DISTINCT CustID)) INTO vValueList INDIC IvValueList FROM InvoiceHeader
100,101,102,103,104,105,106,10
7,108,109,110,111,112,113,114,
115,116,117,118,119,120,121,12
2,123,124,125,126,127,128,129
Example 04:
The following example retrieves a list of DISTINCT state and zip code values from the Employee table.
SELECT DISTINCT EmpState,EmpZip, FROM Employee
EmpState EmpZip
-------- ----------
PA 15668-1854
WA 98004
WA 98072
WA 98101
WA 98102
WA 98103
WA 98115
