Applying an Aggregate Function


Concept


Steps:

  1. Begin by navigating to the Records page.

     

    Click the Reporting Tools link.

    Step 1
  2. Click the Query Manager link.

    Step 2
  3. Click the Create New Query link.

    Step 3
  4. Use the Records page to view existing records. Select an existing record to create a new query.

    Step 4
  5. The first step in creating a query is to find an existing record for the query. In this example, you will locate and use the Student Career Term Table record.

     

    Enter the desired information into the begins with field. Enter

     a valid value
     e.g.
     "STDNT_CAR_TERM"
    .

    Step 5
  6. Click the Search button.

    Step 6
  7. Click an entry in the Add Record column.

    Step 7
  8. The Query page appears, displaying several fields. Use this page to add fields to a query.

    Step 8
  9. Click the EMPLID - EmplID option.

    Step 9
  10. Click the ACAD_CAREER - Academic Career option.

    Step 10
  11. Click the STRM - Term option.

    Step 11
  12. Click the Fields tab.

    Step 12
  13. Use the Fields page to view how fields are selected for output, view the properties of each field, change headings, change column and sort orders, and apply aggregate values.

    Step 13
  14. In this example, you first want to sort the results by Career, then by Term.

     

    Click the Reorder / Sort button.

    Step 14
  15. Use the Edit Field Ordering page to modify the order of the columns.

    Step 15
  16. Click in the New Order By field.

    Step 16
  17. Enter the desired information into the New Order By field. Enter

     a valid value
     e.g.
     "1"
    .

    Step 17
  18. Click in the New Order By field.

    Step 18
  19. Enter the desired information into the New Order By field. Enter

     a valid value
     e.g.
     "2"
    .

    Step 19
  20. Click the OK button.

    Step 20
  21. Next, you want to apply an aggregate function to the EMPLID field to get a count of each ID.

     

    Click the Edit button.

    Step 21
  22. Use the Edit Field Properties page to customize your field properties.

    Step 22
  23. You can apply the following aggregate functions to a field:

    Sum: Adds the values from each row and displays the total.

    Count: Counts the number of rows.

    Min: Checks the value from each row and returns the lowest one.

    Max: Checks the value from each row and returns the highest one.

    Average: Adds the values from each row and divides the result by the number of rows.

     

    In this example, you want a total count for each ID.

     

    Click the Count option.

    Step 23
  24. Click the OK button.

    Step 24
  25. Click the Save As link.

    Step 25
  26. Use the Enter a name to save this query: page to specify a name and description for the new query you created.

    Step 26
  27. Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "CAREER_COUNT"
    .

    Step 27
  28. Click in the Description field.

    Step 28
  29. Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "# students per career/term"
    .

    Step 29
  30. Use the Query Type field to specify the type of query as User, Process, or Role. Standard queries are defined as User types, and queries that use workflow are defined as Process or Role types. For the exercise, retain the default query type.

    Step 30
  31. Use the Owner field to specify the access to this query. Private indicates that only the user ID that created the query can open, run, modify, or delete the query. Public indicates that any user with access to the records used by the query can run, modify, or delete the query. For this example, you want to make it a private query.

    Step 31
  32. Click the OK button.

    Step 32
  33. Finally, view the results of the query.

     

    Click the Run tab.

    Step 33
  34. Use the Run page to view the results of your query.

    Step 34
  35. The results display a count of total IDs for each career and term combination.

    Step 35

You have successfully applied an aggregate function to a query.

Table of Contents  Start Topic