Aggregate Functions

Introduction

Aggregate functions compare and compile information from multiple rows to determine a single value.

Popular aggregate functions include:

  • Count()
  • Max()
  • Min()
  • Sum()
  • Functions with the prefix Running-

Aggregate Functions

All functions in this area must have an object from your report inside the parentheses.

Count()

Syntax: Count([Object_name])

This function counts the rows returned for the selected object. In a table by itself, it will display the total number of rows for the given object. When added to a table, it will split the total into the appropriate number for each row.

Example:

=Count(DV Division Name) in an course report will show the numeral 7 because seven divisions give classes at UCSC:

Screenshot of InfoView count variable results

Troubleshooting: If you notice that your count function is returning a number that is too high, try adding "distinct" to the syntax to make sure it is not counting duplicate values.

Syntax: Count([Object_name];Distinct)

Max()

Syntax: Max([Object_name])

This function returns the highest value of the set retrieved for your report.

Note: This function will also work on letters. For example if you have rows returned that either contain the letter "N" for no and the letter "Y" for yes, a variable asking for the max value in this field will return a "Y" because "Y" comes after "N" in the alphabet.

Min()

Syntax: Min([Object_name])

This function returns the lowest value of the set retrieved for your report.

Note: This function will also work on letters. For example if you have rows returned that either contain the letter "N" for no and the letter "Y" for yes, a variable asking for the max value in this field will return an "N" because "N" comes before "Y" in the alphabet.

Sum()

Syntax: Sum([Object_name])

This function adds numeral values together.

Running-

All of the functions prefaced by the word "Running" will do the same action to the chosen field as the original function, but it will complete the action continually along the side of a table with the counted field in it.

Example Count() vs. RunningCount()

This table shows both variable functions counting D Dept Code while D Dept Code is in the table:

Screenshot of InfoView count vs running count functions

This table shows both variable functions counting D Dept Code but without any other fields in each table:

Screenshot of InfoView count vs running count functions