Using SQL in an Arcade GroupBy expression

JasonBatory

Can anyone confirm whether expressions like this should work, or if my syntax is just wrong?

In my Arcade groupBy function I want to use SQL in the expression, like this:

 

var fs = FeatureSetByPortalItem(
  Portal("https://www.arcgis.com"),
  "6200db0b80de4341ae8ee2b62d606e67",
  0, 
  ["*"], 
  false 
);

//For each building FeatureCode
//Count how many buildings with each FloorCount
return GroupBy(fs, 'FEATURECODE', 
[{name: 'Floors_0', expression: 'FLOORCOUNT = 0', statistic: 'Count'},
{name: 'Floors_1', expression: 'FLOORCOUNT = 1', statistic: 'Count'},
{name: 'Floors_2', expression: 'FLOORCOUNT = 2', statistic: 'Count'},])

 

 This returns an error:

Test execution error: Unknown Error. Verify test data.

 I've tried various different syntax for the 'FLOORCOUNT = 0' statement with the same results.

jcarlson

In GroupBy, the "expression" key is specifically looking for valid SQL.

@JasonBatoryin your series of expressions, you're treating the "expression" like it's a filter. In GroupBy, it's going to attempt to return the statistic from the expression, and you can't really count whether or not the floorcount is equal to a value.

To use @CodyPatterson 's idea, but in SQL, you can use CASE:

"CASE WHEN FLOORCOUNT = 0 THEN 1 ELSE 0 END"

Use that with the statistic "SUM", and you'll get the number of records with that particular floor count value.