Subquery and aggregate function with datatable

[PG_9957352]

Hi,

I have a DataTable in Thingworx storing data from csv spreadsheet.

I need to perform advanced queries with aggregate functions grouped by multiple columns.

Conceptually, my case is similar to this:

https://stackoverflow.com/questions/24657624/aggregate-function-in-group-by-multiple-columns

In particular, the datashape of the DataTable is:

let newEntry = {
   Employee: undefined, // STRING [Primary Key]
   LaborTime: undefined, // NUMBER
   Project: undefined, // STRING [Primary Key]
   Month: undefined // STRING [Primary Key]
};

I need to compute the sum of LaborTime of all Employees for each Month, grouped by Project.

What is the code to perform such query?

The result should then be plotted in BarChart/LineChart widget.

Best,

Pietro

[PaiChung · Accepted solution]

After you query the data from the datatable, there is an infotable function called 'Aggregate' which you can then use.