Projection with sum


#1

I’m trying to perform an evaluate by grouping statuses by location id and then summing up over duration spent at those locations.

I’m running the following query and I’m getting an error:

c3CSV(AircraftStatus.evaluate({
projection: “sum(duration.value), location.id”,
group: ‘location.id’,
order: “descending(sum(duration.value))”
}))

“Can’t group on a projection that isn’t handled by the db engine: ‘location.id’”

However I can do this:
c3CSV(AircraftStatus.evaluate({
projection: “count(), location.id”,
group: ‘location.id’,
order: “descending(count())”
}))

What is the right syntax for sum / is it supported?


Is there an equivalent SQL array_agg grouping function in expression engine
#2

This post about grouping may provide some insight: Evaluate with group on timeComponent


#3

I suspect it has something to do with summing on a Dimension value. Can you file a ticket and include the following details and I’ll look into it:

  1. url/tenant/tag to reproduce on live system if possible
  2. branch/tenant/tag to provision from to reproduce

#4

This is really not a bug. Since AircraftStatus.duration is a Dimension (e.g. each value can have a different unit), the db engine can’t send the query to the database since it wouldn’t know how to do the math involving unit conversions so it gets evaluated by the expression engine. Since the expression engine can’t do grouping you get this error. In this case, I’m assuming that the duration values are all of the same unit. Assuming that, you can do the query as follows:

AircraftStatus.evaluate({
projection: “sum(duration.value), location”,
group: ‘location’,
order: “descending(sum(duration.value))”,
forceDbEngineEval: true
})

This will send the query to the database and do unitless math on the sum. If the durations are all the same unit, perhaps that field could have just been an appropriate numeric type rather than a dimension. If that were the case, you wouldn’t run into this.