Using the Group Function in a Query

From NeoWiki

Avid birders keep a life list. A life list is a list of bird species the birder has identified. To be an adequate tool for birders, our database needs to include a means for creating a life list. We took a step in that direction when we created the All Sightings query. If you have entered the test data provided in the article Creating A Data Entry Form With A Subform, however, you will notice that certain bird species (e.g. the Tufted Timouse) appear twice in the list. For a serious birder who makes regular field trips, there would quickly be even more entries for each species in this list. We need to construct a query that will list each bird species only once.

Creating the Query

1. In the main database document, click on the Queries icon in the left hand column.
2. Click on the Create Query in Design View icon in the Tasks section. The Query Design window opens and the Add Table or Query window opens.
3. In the Add Table of Query window, click on the Queries radio button
4. Select the Sightings List query and click Add
5. Close the Add Table of Query window by clicking Close
6. Adjust the Sightings mini-window as necessary to see the full list of fields.
7. Add the following fields to the Query, in the order given:

   AOU_ID

   Bird_Name

   Date

   Date (you will have two columns headed with the Date field)

8. In the Function row of the AOU_ID and Bird_Name columns, select Group from the dropdown. (You need to click in the cell before the dropdown appears.)
9. In the Sort row, select ascending under the AOU_ID column.
10. In the Function row, under the first Date column, select Minimum from the dropdown.
11. In the Alias row, under the first Date column, type DateFirstSeen
12. In the Function row, under the second Date column, select Count from the dropdown.
13. In the Alias row, under the second Date column, type SightingsCount
14. Click the Run Query button on the toolbar. (It is the data sources icon with a green arrow pointing down). After a pause, you should see the query results.

    • The rows will be sorted by AOU_ID order, and there will only be one entry for each bird species.
    • Note that the DateFirstSeen column will contain integers, not dates. There are three ways to deal with this:

    1. Control-click or right-click on the DateFirstSeen column of the query results and choose Column Format... from the contextual menu. Set the Category to Date and choose the Desired Format. Click OK. (This works even if the Query hasn't been opened in design mode)
    2. Use the CAST function as described below
    3. Correct the issue only in forms using the query by using a date or formatted control for the DateFirstSeen field. This is left as an exercise for the student.

    • The SightingsCount column indicates how many times a given bird has been identified.

15. Save your query by clicking on the Save button on the toolbar or hitting Command-S.
16. In the window that appears, give your query the name Life_List
17. Click OK

Using CAST in SQL Direct Mode

The CAST function allows you to "convert" a field from one type to another. In our example, we want to CAST an integer (the MIN(DATE) result) as a Date. But this function can also be used to CAST a numeric field as text, etc. Unfortunately, the CAST function cannot be used in Query Design mode (the mode where you see columns for each field and rows for Alias, Table, Criteria, etc.). CAST can only be used in SQL mode, and specifically in SQL direct mode. Queries using SQL direct mode cannot be viewed in Design mode.

1. With the Query built above still open in editing mode, click on the Design View On/Off Button

   The Query building section of the window shifts to SQL mode and you will see several lines of SQL code that look similar to this:

   SELECT "AOU_Birdlist"."AOU_ID", "Sightings"."Bird_Name", MIN( "Field_Trips"."Date" ) AS "DateFirstSeen", COUNT( "Field_Trips"."Date" ) AS "Count" FROM "Sightings", "AOU_Birdlist", "Field_Trips" WHERE ( "Sightings"."Bird Name" = "AOU Birdlist"."EnglishName" AND "Sightings"."Field Trip ID" = "Field Trips"."ID" ) GROUP BY "AOU Birdlist"."ID", "Sightings"."Bird Name"

2. Click on the Run SQL command directly button on the toolbar. It is a data sources icon with a green arrow pointing down.
3. Find the MIN("Field_Trips"."Date) and edit it to read:

   CAST(MIN("Field_Trips"."Date) AS DATE)

   Do not change anything else in the Query unless you understand SQL!

4. Click on the Run Query button on the toolbar to test the Query.
5. You can now close the Query

Other Notes

• The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above.
• You cannot add fields to a Group query that are not assigned a function.
• If a Grouping Query is based on a Query that contains multiple OR statements, NeoOffice (and OpenOffice.org) will hang when you attempt to run the Query. See Using a Union Statement in a View for an alternative method for building the underlying Query in these cases