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

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

where+groupby on infotable/datatable

vijay5
10-Marble

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
raluca_edu
17-Peridot
(To:vijay5)

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
22-Sapphire I
(To:vijay5)

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
17-Peridot
(To:PaiChung)

@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

Top Tags