It is possible to do it all in a single GroupBy if you write the right expression. Look at the following SQL expression:

```
CASE WHEN obs1 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs2 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs3 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs4 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs5 = 'Tree Obstruction' THEN 1 ELSE 0 END
```

If your point had 4 of the 5 fields marked as "Tree Obstruction", this will return a 4. And if you use this in GroupBy with the statistic **SUM**, you'll end up with the total of all Tree Obstructions across 5 fields for the whole layer.

The trouble is that you'd need the same 5-line expression for every item in your domain, even though only the domain value is changing. Not that that's a bad thing, but it makes the expression lengthy and annoying.

Now consider a custom function:

```
function GroupingExpression(domain_value) {
return `
CASE WHEN obs1 = '${domain_value}' THEN 1 ELSE 0 END +
CASE WHEN obs2 = '${domain_value}' THEN 1 ELSE 0 END +
CASE WHEN obs3 = '${domain_value}' THEN 1 ELSE 0 END +
CASE WHEN obs4 = '${domain_value}' THEN 1 ELSE 0 END +
CASE WHEN obs5 = '${domain_value}' THEN 1 ELSE 0 END
`
}
```

Now we can just call GroupingExpression('Tree Obstruction') to get the same thing as before. And if we use it to build our GroupBy function, it could look like this:

```
GroupBy(
fs,
{name: 'the_row', expression: 1},
[
{name: 'tree_obstruction_count', expression: GroupingExpression('Tree Obstruction'), statistic: 'SUM'},
{name: 'always_off_count', expression: GroupingExpression('Always Off'), statistic: 'SUM'},
// and so on
]
)
```

The result of this should be a single-row FeatureSet with the totals in their own columns, which you can then use to generate a chart.

- Josh Carlson

Kendall County GIS