cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

where+groupby on infotable/datatable

Level 7

where+groupby on infotable/datatable

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

 

 

3 REPLIES 3

Re: where+groupby on infotable/datatable

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

Re: where+groupby on infotable/datatable

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

Re: where+groupby on infotable/datatable

@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