Please enable JavaScript to view this site.

R:BASE 11 Help

Navigation: Command Index > W > WHERE

GROUP BY

Scroll Prev Top Next More

This clause determines which rows of data to include.

 

WHERE_GROUPBY

 

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 commands.

 

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 command.

 

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

 

transid

custid

empid

netamount

4975, 4980, 5000, 5060, 5045

101, 101, 101, 101, 100

102

$87,500, $22,500, $40,500, $57,500, $3,060

4790, 4865, 5050, 5070

104, 102, 104, 104

129

$6,400, $34,125, $56,250, $95,500

5015

103

131

$80,500

4760, 5080, 5048

100, 100, 103

133

$32,400, $88,000, $12,740

4780

105

160

$9,500

5046, 5049

101, 102

165

$3,060, $26,310

5047

102

167

$3,830

 

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

 

transid

custid

empid

netamount

5045

100

102

$3,060

4975, 4980, 5000, 5060

101

102

$87,500, $22,500, $40,500, $57,500

4865

102

129

$34,125

4790, 5050, 5070

104

129

$64,000, $56,250, $95,500

5015

103

131

$80,500

4760, 5080

100

133

$32,400, $88,000

5048

103

133

$12,740

4780

105

160

$9,500

5046

101

165

$3,060

5049

102

165

$26,310

5047

102

167

$3,830

 

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

 

empid

emplname

hiredate

emptitle

102

Wilson

03/18/90

Manager

129

Hernandez

08/28/91

Manager

131

Smith

04/14/92

-0-

133

Coffin

11/26/93

Representative

160

Simpson

01/09/94

-0-

165

Williams

07/05/92

Representative

167

Watson

07/10/92

Representative

166

Chou

07/10/93

Sales Clerk

 

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

 

empid

emplname

hiredate

emptitle

102, 129

Wilson, Hernandez

03/18/90, 08/28/91

Manager

133, 165, 167

Coffin, Williams, Watson

11/26/93, 07/05/92, 07/10/92

Representative

166

Chou

07/10/93

Sales Clerk

131, 160

Smith, Simpson

04/14/94, 01/09/94

-0-