Chapter Contents

Previous

Next

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 list of Available Tables and add it to the list of Selected Tables. Select OK.

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

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.

Select OK.

Grouping Columns Automatically

Select

Tools

Run Query

Run Immediate

A dialog is displayed.

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

A second dialog is displayed.

Select NO. The automatic Group By clause will be part of the query syntax while the query runs, but 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

Tools

Reset

to reset your query. A dialog appears.

Select OK to return to the SQL QUERY TABLES window. The components of the current query are cleared.

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 list of Available Tables and add them to the list of Selected Tables.

Select OK.

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

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.

Select JOBCODES.JOBCODE from the Available Columns List.

Select OK to return to 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.

Retaining an Automatic Group By as Part of a Query

Select

Tools

Run Query

Run Immediate

A dialog is displayed. Select AUTOGROUP in the dialog window to use JOBCODES.TITLE as the Group By column. A second dialog is displayed. Select YES in the second dialog window 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

Tools

Show 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

File

Save Query

Save as Query to Include later

In the Entry Name field, type COUNTS as the name of the query. In the Enter a description for the query field type COUNT OF EMPINFO 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 list of Selected Tables. Select OK in the pop-up dialog.

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.