We’ve extended Select method of TeamDesk REST API with an ability to perform aggregate functions over the columns’ values bringing functionality in par with now-obsolete SOAP API.
Now, you can instruct REST API to calculate aggregate functions by adding //<FUNCTION> suffix to column’s name in a list of columns to select, such as: column=Population//SUM. <FUNCTION> is one of the following: COUNT, SUM, AVG, MIN, MAX, STDEV, STDEVP, VAR, VARP (uppercase is required).
Columns to group by are specified by appending //<GROUPBY> suffix to column name.
You can choose from the following <GROUPBY> values, depending on column type:
- All types:
- EQ – by equal values.
- Text
- FW – by first word.
- FL – by first letter.
- Times, Durations and Timestamps
- SS – by second.
- MI – by minute.
- HH – by hour.
- Dates, Durations and Timestamps
- DD – by day.
- Dates and Timestamps
- MM – by month.
- QQ – by quarter.
- YY – by year.
- Numbers
- .001 – by one thousandth
- .01 – by one hundredth
- .1 – by one tenth
- 1 – by integer value
- 10 – by ten
- 100 – by hundred
- 1K – by thousand
- 10K – by ten thousands
- 100K – by hundred thousands
- 1M – by million
If you specified aggregate function for one of the columns, all columns you are not aggregating should contain group specifier. Also, in this case sorting is limited to the columns you group by.
Results with aggregate functions
Since you can calculate multiple aggregate function over a single column in one query, the value appears under the name suffixed function name in the result. However, group columns appear without the suffix since you can not group over the same column twice. Last row of the output is a grand total row designated by “@row.type“: “Grand” in JSON (or type=”Grand” attribute in XML) containing aggregate values calculated across all groups, for example,
.../Invoices/select.json?column=Customer//EQ&column=Invoiced//SUM
produces
[
{ "Customer": "Oracle", "Invoiced//SUM": 10000 },
{ "Customer": "Google", "Invoiced//SUM": 20000 },
{ "Customer": "Microsoft", "Invoiced//SUM": 50000 },
{ "@row.type": "Grand", "Customer": null, "Invoiced//SUM": 80000 }
]
As a final word, as slash can not appear in the name of XML tags and requires encoding you can use two minus signs instead of two slashes as a separator.
Enjoy!
