ORDA provides a great feature to channel data through filters and get aggregated values, by including a set of easily-understood aggregation operations that examine and perform calculations on the data sets. For example, the sum, average, count, min, and max methods are used to perform the operations which their names describe!
Let’s get our hands dirty and see how to use these methods!
The following table is named Mileage:
This table is composed of three fields ID, Date and Miles, with one record per day. We’re going to look at five interesting numbers from this data set:
- How many dates are in the table
- The total number of miles
- The average number of miles per day
- The beginning of the period in the table
- The ending of the period in the table
Count the number of dates in the table
If you want to know how many dates are present, you just need to tally the lines in your table. Use the count() method, which returns the number of not null data in a given column:
$count:=ds.Mileage.all().count("Date") // $count=7
Sum of all the Miles values
To calculate the total number of miles, you need to add up everything in the “Miles” column. Use the sum() method, which returns the sum of all the values in a given column:
$total:=ds.Mileage.all().sum("Miles") // $total=3069.7
average number of miles
Now we’ll to find the mean of miles. We could use the sum() and count() methods to calculate it ourselves, but ORDA has a method just for this: average()
$avg:=ds.Mileage.all().average("Miles") // $avg=438.528
Beginning and ending of a period
And finally, to find the date range in table, we simply need to find the minimum and maximum values in the Date column. The easiest way is to use the min() and max() methods:
$dateMin:=ds.Mileage.all().min("Date") // $dateMin=14/03/2018
$dateMax:=ds.Mileage.all().max("Date") // $dateMax=20/03/2018
More examples of aggregate methods are available in the database example.