Transform to Compute Statistics
There are some built-in transform classes to compute simple statistics.
Example: you want to find the maximum score across all player’s entries.
[cfdb-table form="games" trans="MaxField(scores)"]
This returns one row in your result like this:
| score |
|---|
| (computed max) |
Example: you want to find each person’s maximum score across a number of entries for each person’s name. This is like “max score grouped by name”
[cfdb-table form="games" trans="MaxField(scores,name)"]
This returns one row per unique name in your result with
| name | score |
|---|---|
| John | 15 |
| Richard | 16 |
You can so the same with these classes or build your own:
- CountField
- SumField
- MinField
- MaxField
- AverageField
What if you want to “group by” more than one field?
Then create a new field that includes the information from all your fields, then “group by” it.
[cfdb-table form="games" trans="full_name=concat(first_name,' ',last_name)&&MaxField(scores,full_name)"]
| name | score |
|---|---|
| John Doe | 15 |
| Richard Roe | 16 |
Notice the single-quoted space (‘ ‘) in “concat”. Single quote whitespace or it will be ignored.
Note: The “concat” function is provided by CFDB since there is no function for concatenation in PHP (it use the “.” operator).
Counting Checkbox Values
In the case where you have a checkbox field with multiple selections, you may have data like this:
|
Count the votes:[cfdb-table form="voteform" trans="CountInField(Vote,Count)"] |
| Vote | Count |
|---|---|
| AA | 2 |
| FF | 1 |
| CC | 2 |
| DD | 1 |
| EE | 1 |
| GG | 1 |
| HH | 1 |
[cfdb-table form="voteform" trans="CountInField(Vote,Count)&&NaturalSortByField(Count,DESC)"]
If the values were separated with something other than a comma, such as “|” (e.g. “CC|DD|EE”) then indicate the delimiter:
[cfdb-table form="voteform" trans="CountInField(Vote,Count,|)"]
Add a column with a row number in it (as of version 2.10.10)
AddRowNumberField(fieldname,start) where “fieldname” is the column header and “start” (optional) is the number to start the count at (by default 1)
[cfdb-table form="games" trans="AddRowNumberField(#)"]
| name | score | # |
|---|---|---|
| John | 5 | 1 |
| John | 6 | 2 |
| John | 7 | 3 |
| Richard | 2 | 4 |
But that adds it at the end. You can move it to the beginning using “show”. Here we use start to indicate the “#” column followed by everything else:
[cfdb-table form="games" show="#,/.*/" trans="AddRowNumberField(#)"]
| # | name | score |
|---|---|---|
| 1 | John | 5 |
| 2 | John | 6 |
| 3 | John | 7 |
| 4 | Richard | 2 |
The SummationRow transform does this (as of version 2.9.12).
[cfdb-table form="games" trans="SummationRow(score)"]
| name | score |
|---|---|
| John | 5 |
| John | 6 |
| John | 7 |
| Richard | 2 |
| 20 |
Sum Multiple Rows
[cfdb-table form="games" trans="SummationRow(score1,score2)"]
| name | score1 | score2 |
|---|---|---|
| John | 5 | 4 |
| John | 6 | 7 |
| John | 7 | 9 |
| Richard | 2 | 1 |
| 20 | 21 |
Add label “Total” to in the Name column
[cfdb-table form="games" trans="SummationRow(score1,score2,name:Total)"]
| name | score1 | score2 |
|---|---|---|
| John | 5 | 4 |
| John | 6 | 7 |
| John | 7 | 9 |
| Richard | 2 | 1 |
| Total | 20 | 21 |