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.

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.

Select OK.

Select ToolsRun QueryRun Immediate. A dialog box appears.

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.

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.

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

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 ViewWhere Conditions for Subset.

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

Select JOBCODES.JOBCODE from the Available Columns list.

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.

Select OK.

Select ToolsRun QueryRun 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.

In the SQL QUERY COLUMNS window, select ToolsShow Query.

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 FileSave QuerySave 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 ViewTables to return to the SQL QUERY TABLES window. Remove SALARY.JOBCODES from the Selected Tables list. Select OK in the dialog box that appears.