Examples

Summarizing Groups of Data

Summary functions produce a statistical summary of a table or groups of data. The following example displays the minimum, average, and maximum level of employee education within each division. Use the Group By clause and a summary function to summarize information about a group of data. If you omit a Group By, then one summary value is produced for the entire table.

Summary Functions

The Selected Tables list in the SQL QUERY TABLES window contains SAMPLE.EMPINFO from the previous example. Select OK.

In the SQL QUERY COLUMNS window, remove COUNT(*) from the Selected Columns list. Select DIVISION and Education level from the Available Columns list and add them to the Selected Columns list.

Select Education level a second time from the Available Columns List and add it to the Selected Columns list.

Select Education level a third time from the Available Columns list and add it to the Selected Columns list.

[Selecting columns to summarize]

Select the first Education level from the Selected Columns list. Select Summary Functions.

[List of summary functions]

Select MIN from the list of summary functions. A summary function is applied to the selected column and a default unique column alias is automatically generated. The summary function and the selected column name are automatically set as the label. You can use this default label in the report, or you can set a new alias or label.

Select the second Education level from the Selected Columns list. Select Summary Functions. Select AVG from the list of summary functions.

Select the third Education level from the Selected Columns list. Select Summary Functions. Select MAX from the list of summary functions.

Select the first Education level from the Selected Columns list. Select Column Alias/Label. Type Minimum Years of Education in the Label field of the Column Alias and Label window.

[Assigning a column label]

Select OK.

Select the second Education level from the Selected Columns list. Select Column Alias/Label. Type Average Years of Education in the Label field of the Column Alias and Label window.

Select OK.

Select the third Education level from the Selected Columns list. Select Column Alias/Label. Type Maximum Years of Education in the Label field of the Column Alias and Label window.

Select OK.

Select the second Education level from the Selected Columns list. Select Column Formats.

[Column Formats dialog box]

Type comma4.0 in the Format field. Select OK.

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

Group By Columns

Select GROUPBY to display the GROUP BY COLUMNS window.

[GROUP BY COLUMNS window]

Select DIVISION from the Available Columns list and add it to the Group By Columns list. Select OK.

The minimum, average, and maximum education levels of the employees for each division are displayed in the Output window.

[Query output]

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

Removing Duplicate Rows

You can remove duplicate rows from your query output. To display each distinct division and location, select SAMPLE.EMPINFO and add it to the Selected Tables list. Select OK.

In the SQL QUERY COLUMNS window, select DIVISION and LOCATION and add them to the Selected Columns list.

Select View Distinct.

Select Tools Run Query Run Immediate. Lines in the Output window that contain the same division and location are not repeated.

[Query output]

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

Top of Page