Select (Table) Method with aggregation
Select (Table) method is also capable to calculate aggregate function over the data group or complete data set.
To perform calculation, add double slash and a suffix that indicates a function to calculate to a column name, such as Number//MAX. Functions available are:
| Function | Types | Description |
|---|---|---|
| COUNT | All types | Calculates count of records in a group or complete set. |
| SUM | Numeric, Duration | Calculates total of numeric/duration column over a group or complete set. |
| AVG | Numeric, Duration | Calculates average of numeric/duration column over a group or complete set. |
| MIN | All types | Calculates minimum value in a column over a group or complete set. |
| MAX | All types | Calculates maximum value in a column over a group or complete set. |
| STDEV | Numeric, Duration | Calculates statistical deviation of all values in numeric/duration column over a group or complete set. |
| STDEVP | Numeric, Duration | Calculates statistical deviation for the population of all values in numeric/duration column over a group or complete set. |
| VAR | Numeric, Duration | Calculates statistical variance of all values in numeric/duration column over a group or complete set. |
| VARP | Numeric, Duration | Calculates statistical variance for the population of all values in numeric/duration column over a group or complete set. |
To set column(s) to group by add double slash and a grouping suffix to a column name(s):
| Group by | Types | Description |
|---|---|---|
| EQ | All types | Group by equal value |
| FW | Text | Group by first word |
| FL | Text | Group by first letter |
| SS | Duration, Time, Timestamp | Group by second |
| MI | Duration, Time, Timestamp | Group by minute |
| HH | Duration, Time, Timestamp | Group by hour |
| DD | Date, Duration, Timestamp | Group by day |
| MM | Date, Timestamp | Group by month |
| Date, Timestamp | Group by quarter | |
| YY | Date, Timestamp | Group by year |
| .001 | Numeric | Group by one thousandth |
| .01 | Numeric | Group by one hundredth |
| .1 | Numeric | Group by one tenth |
| 1 | Numeric | Group by integer value |
| 10 | Numeric | Group by ten |
| 100 | Numeric | Group by one hundred |
| 1K | Numeric | Group by one thousand |
| 10K | Numeric | Group by ten thousands |
| 100K | Numeric | Group by hundred thousands |
| 1M | Numeric | Group by one million |
If function to calculate is specified all the other columns must contain group type suffix.
By default, group values are sorted in ascending order. To reverse direction, specify column//DESC via sort parameter.
GET https://www.teamdesk.net/secure/api/v2/21995/Test/select.json?column=Date//MM&column=Number//SUM&sort=Date//DESC
When group columns are present, first row of the output, indicated by "@row.type": "Grand" (or type="Grand" attribute of the row element in XML) contains value calculated over all groups.