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 |