I'm trying to query some data using ESRI JS API version 4.0. The data table that looks like a longer version of this:
|
|
|
---|---|---|
1 | "MH" | 5 |
2 | "MH" | 3 |
3 | "IN" | 3 |
4 | "IN" | 2 |
I am trying to group this data based on the item_ty_co and Serv_grade fields, and count the number of records in each group.
In regular SQL, my query looks like:
SELECT Item_ty_co, Serv_grade ,count(Object_ID) FROM public."pointAssets" GROUP BY Item_ty_co ,Serv_grade HAVING ITEM_TY_CO not in ('PN','GN','RN','CN')
which gives me exactly the result I want. However, I want to recreate this in ESRI JS API by querying the layerview like this:
const query = layerView.layer.createQuery(); query.outStatistics = [ { onStatisticField: "Object_ID", outStatisticFieldName: "numberOfAssets", statisticType: "count" },]; query.groupByFieldsForStatistics = ["Item_ty_co ","Serv_grade" ]; query.having = "ITEM_TY_CO NOT IN ('PN','GN','RN','CN')"
However, I'm having two problems here.
So the output for the above is an array of objects like this, with one record for the unique ITEM_TY_CO's:
0: Object { numberOfAssets: 4176, ITEM_TY_CO: "PN" } 1: Object { numberOfAssets: 4662, ITEM_TY_CO: "MH" } 2: Object { numberOfAssets: 5472, ITEM_TY_CO: "CN" }
I don't know where my mistakes are. I can write workarounds, but I'd really like a clean solution. Any help/ advice would be greatly appreciated.