GROUP BY

GROUP BY

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 1 to 18 character column name. 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 groups 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

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-