2 Click the Select Distinct Values option to active it.

Related Topics

DISTINCT, Page 551

How to summarize data with aggregate functions

Generates GROUP BY clause

The GROUP BY clause retrieves a set of summary data.

Aggregate functions

Use aggregate functions to obtain summary information on all records or on groups of records. Aggregate functions are most useful when you do not need the detail information and only want to examine totals.

For example, you might need to know the total number of orders made and the average amount of each order. For this kind of query, you apply the COUNT function to the Order ID field, and the AVG (average) function to the Amount field. The query calculates the summary information and provides the results you need.

Click the Fields Tab of the Create Query Expert.

478	Seagate Crystal Reports User’s Guide

2 Highlight the field you want to summarize from this list.

!In the Total drop-down box, choose an aggregate function to apply to the highlighted field.

●COUNT( ) counts the number of values within a group.

●SUM( ) adds together the values within a group for a total.

●AVG( ) finds the average value of all values within a group.

●MIN( ) finds the minimum value within a group.

●MAX( ) finds the maximum value within a group.

"The query summarizes the field that the aggregate function is applied to. Any other fields that appear in the Query Fields list box are used to sort the data.

Related Topics

GROUP BY, Page 553

How to sort records according to field values

Generates ORDER BY clause

The ORDER BY clause indicates that the database records retrieved be sorted according to the values in a specific field.

Queries

479

Query data can be grouped either by sorting data, so that records with like data appear grouped in the sorted list, or by summarizing data with aggregate functions, so that summary data appears in your query for each group of records. For information on using aggregate functions in your query, see How to summarize data with aggregate functions, Page 478. This section shows you how to group data by sorting.

Click the Sort Tab in the Create Query Expert.

2 Highlight the field you			3 Click the		Use up/down arrows to
want sorted from this list.			Add	button.	move fields up/down the list.
						The field will
						be added to
						the Group
						Fields list.

4 Select the order in which you want the data sorted.

Related Topics

ORDER BY, Page 552

How to specify records to be included in a query

Generates WHERE clause

The WHERE clause can specify record selection criteria.

Click the Select Tab of the Create Query Expert.

480	Seagate Crystal Reports User’s Guide

2 Highlight the field			3 Click the Add button.		The field will be added to
which contains the data					the Select Fields list.
you want to base selection
criteria on from this list.

Use these controls to specify the selection criteria to be applied to the field highlighted in the Select Fields dialog box.

These choices work much like the Select Expert in Seagate

Crystal Reports. Search for Select Expert in Crystal Query

Designer online Help.

"Repeat Steps 2 and 3 for every field that appears in the Select Fields list box.

Related Topics

WHERE, Page 552

How to select groups to be included in a query

Generates GROUP BY and HAVING clauses

The HAVING clause creates selection criteria for the summary information produced by the GROUP BY clause.

Click the Fields Tab of the Create Query Expert.

Queries

481

2 Highlight the field you want to summarize in this list.

3 Select the aggregate function from this drop-down list that you want to apply to the highlighted field.

"	Click the Select Tab.
5	Select the aggregate	6 Click the Add button...
function from this list box.

The field will be added to the Select Fields list.

7 Use the selection criteria controls to specify which group summary values based on the aggregate function should appear in the result.

482	Seagate Crystal Reports User’s Guide