Examples

Counting and Grouping Data Automatically

You can count and report the total number of rows that have the same value for one or more columns. You can use the automatic group-by feature to group the values according to their columns.

The following query displays the number of employees in each division.

In the SQL QUERY TABLES window, select SAMPLE.EMPINFO from the Available Tables list and add it to the Selected Tables list. Select OK.

In the SQL QUERY COLUMNS window, select DIVISION and < COUNT(*) > from the Available Columns list and add them to the Selected Columns list.

Count

Select COUNT(*) from the Selected Columns List. Select Move After to move the column. Reselect COUNT(*). Select Column Alias/Label. Type Count of Employees for Each Division in the Label field of the Column Alias and Label window.

[Specifying a label for COUNY(*)]

Select OK.

Grouping Columns Automatically

Select Tools Run Query Run Immediate. A dialog box appears.

[Selecting a grouping method]

Select AUTOGROUP to automatically select the correct columns. Selected columns that do not have summary functions applied to them will be the group or groups that the summary functions are computed for.

A second dialog box appears.

[Selecting whether to permanently save automatic grouping method]

Select No. The automatic Group By clause will be part of the query syntax while the query runs, but it will not be retained. You can select or remove columns after the query is executed and use AUTOGROUP to automatically select the columns again.

The count of employees for each division is displayed in the Output window.

[Query output]

In the SQL QUERY COLUMNS window, select Tools Reset to reset your query. Select OK from the dialog box that appears.

Automatic Group By with More Than One Table

The next query joins two tables to display the number of employees for each job title. The JOBCODES table contains the job title for each job code.

Select SAMPLE.JOBCODES and SAMPLE.EMPINFO from the Available Tables list and add them to the Selected Tables list.

Select OK.

In the SQL QUERY COLUMNS window, select TITLE and < COUNT(*) > from the Available Columns list and add them to the Selected Columns list.

Select View Where Conditions for Subset.

In the WHERE EXPRESSION window, select EMPINFO.JOBCODE from the Available Columns list. Select EQ from the list of comparison operators.

[Building the WHERE expression]

Select JOBCODES.JOBCODE from the Available Columns list.

[Complete WHERE expression]

Select OK to return to the SQL QUERY COLUMNS window.

Select COUNT(*) from the Selected Columns List. Select Move After to move the column. Reselect COUNT(*). Select Column Alias/Label. Type Count of Employees for Each Title in the Label field of the Column Alias and Label window.

[Assigning a column label]

Select OK.

Retaining an Automatic Group By as Part of a Query

Select Tools Run Query Run Immediate. A dialog box appears. Select AUTOGROUP in the dialog box to use JOBCODES.TITLE as the Group By column. A second dialog box appears. Select Yes in the second dialog box to retain the Group By column as part of the query.

The Output window displays the number of employees for each job title.

[Query output]

In the SQL QUERY COLUMNS window, select Tools Show Query.

[SQL QUERY window with SQL statements]

The automatic Group By will be retained as part of the query syntax when the query is run again, saved, or used to create a table or view. Select Goback to return to the SQL QUERY COLUMNS window.

In the SQL QUERY COLUMNS window, select File Save Query Save as QUERY to Include later.

In the Entry Name field, type COUNTS as the name of the query. In the description field, type Count of EMPNO by TITLE . Select OK to save the query and return to the SQL QUERY COLUMNS window.

Select View Tables to return to the SQL QUERY TABLES window. Remove SALARY.JOBCODES from the Selected Tables list. Select OK in the dialog box that appears.

Top of Page