where+groupby on infotable/datatable

[vijay5]

Hello Team,

I need to get count of specified values(where in sql) on a column groupby some other column from infotable.

Any help will be appreciated!!

Thanks

[raluca_edu]

Hi,

Example for a Thing having a property INFOTABLE info1 (datasahape has fields : age, name, id)

Service (input: nothing, output: number ) that gets the number of persons under age 20:

var query = {
sorts:
[{ fieldName: "age",
isAscending: false
}],
filters:
{ type: "AND",
filters:
[{
type: "LT",
fieldName: "age",
value: 20
}]
}
};

var resultTable = Resources['InfoTableFunctions'].Query({
t: me.info1/* INFOTABLE */,
query: query /* QUERY */
});

var result= resultTable.rows.length;

Hope it helps,

Raluca Edu

[PaiChung]

There is an InfoTable function called Aggregate that you can use

it has the Aggregate types of COUNT, AVERAGE, MIN, MAX, SUM

it also has grouping columns you can designate.

It will then produce a new table with your grouping columns and fields named SourceFieldName_COUNT

etc. based on what you want to aggregate

[mgoel]

@vijay5

Hope you are doing good. Could you please confirm if the issue has been resolved.

If yes, please mark the answer as accept as solution for the future reference. Thank you in advance.

Regards-Mohit Goel