R:BASE 11 Help

This clause determines which rows of data to include.

 

 

Options

 

,

Indicates that this part of the command is repeatable.

 

ASC

DESC

Specifies whether to sort a column in ascending or descending order.

 

colname

Specifies a column name. The column name is limited to 128 characters.

 

In a command, you can enter #c, where #c is the column number shown when the columns are listed with the LIST TABLES command. In an SQL command, a column name can be preceded by a table or correlation name and a period (tblname.colname).

 

GROUP BY

Returns a group of rows as a summary resulting in only unique rows. This option is generally used with SELECT Functions.

 

HAVING clause

Limits the rows affected by the GROUP BY clause.

 

ORDER BY clause

Sorts rows of data.

 

About the GROUP BY command

 

This optional clause groups rows according to the values in one or more columns and sorts the results. GROUP BY consolidates the information from several rows into one row. This results in a table with one row for each value in the named column or columns and one or more values per column.

 

The columns listed in the GROUP BY clause are related to those listed in the command clause. Any column named in the GROUP BY clause can also be named in the command clause, but any column not named in the GROUP BY clause can be used only in the command clause if the column is used in a SELECT function.

 

Examples

 

The SELECT command clause can contain the columns named in the GROUP BY clause, and SELECT functions that refer only to columns not named in the GROUP BY clause. Because the GROUP BY clause processes information resulting from a WHERE clause, you can add a GROUP BY clause to see the sales each employee has made:

 

SELECT empid FROM transmaster WHERE netamount < $100,000 +

GROUP BY empid

 

The following intermediate result table contains columns not named in the command clause because the command clause has not been processed yet (not all the columns fit in the display, however). The first part of the processing is to group the rows by empid. Because seven different employees are included, the intermediate result table includes seven rows.

 

Intermediate Result Table-GROUP BY empid

 

 

You can include more than one column in a GROUP BY clause. If you group the rows in the above example by custid as well as empid, the command looks like this:

 

SELECT empid, custid FROM transmaster +

WHERE netamount < $100,000 GROUP BY empid, custid

 

In the following table, rows are now grouped by both empid and custid, resulting in eleven groups.

 

Intermediate Result Table-GROUP BY empid and custid

 

 

If one or more of the columns named in the GROUP BY clause contain null values, R:BASE forms a separate group for null values. Review the result of this SELECT command for the employee table:

 

SELECT empid, emplname, hiredate, emptitle FROM employee

 

 

If you group these rows by the emptitle column, which contains null values, you get the following intermediate result table:

 

Intermediate Result Table-GROUP BY emptitle