AggregateNetworkObjects "mode" statistic isn't working

[jc-harrison]

When I attempt to run an AggregateNetworkObjects query with statistic="mode", I get the error sqlalchemy.exc.ProgrammingError: (psycopg2.errors.WrongObjectType) WITHIN GROUP is required for ordered-set aggregate mode.

To Reproduce

tno = TotalNetworkObjects("2016-01-01", "2016-01-07", total_by="minute", level="admin3")
ano = AggregateNetworkObjects(total_network_objects=tno, statistic="mode")
result = ano.store().result()

gives me the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.WrongObjectType) WITHIN GROUP is required for ordered-set aggregate mode
              LINE 3:         SELECT pcod, mode(z.total) as mode,
                                           ^

              [SQL: EXPLAIN (ANALYZE TRUE, TIMING FALSE, FORMAT JSON) CREATE TABLE cache.xb2aa4b062fa0f013bba5edb08fe76a7c AS
                      (SELECT pcod, mode, datetime FROM (
                      SELECT pcod, mode(z.total) as mode,

                      date_trunc('hour', z.datetime) as datetime FROM
                  (
              SELECT pcod, COUNT(*) as total,
                   datetime FROM
                    (SELECT DISTINCT pcod, location_id, datetime FROM
                      (SELECT pcod, location_id, date_trunc('minute', x.datetime) AS datetime
                      FROM (
              SELECT
                  l.datetime, l.location_id,
                  sites.pcod
              FROM
                  (SELECT events.calls.datetime, events.calls.location_id

              FROM events.calls
              WHERE events.calls.datetime >= '2016-01-01 00:00:00' AND events.calls.datetime < '2016-01-07 00:00:00') AS l
                      INNER JOIN
                          (
                      SELECT
                          location_id, version, date_of_first_service, date_of_last_service,
                          admin3pcod AS pcod
                      FROM
                          (
                          SELECT
                              locinfo.id AS location_id,
                              locinfo.version,
                              locinfo.date_of_first_service,
                              locinfo.date_of_last_service,
                              polygon.admin3pcod

                          FROM
                      infrastructure.cells AS locinfo
                  INNER JOIN
                      geography.admin3 AS polygon
                  ON ST_within(
                      locinfo.geom_point::geometry,
                      ST_SetSRID(polygon.geom, 4326)::geometry
                  )
                  ) AS map
              ) AS sites
              ON
                  l.location_id = sites.location_id
                AND
                  l.datetime::date BETWEEN coalesce(sites.date_of_first_service,
                                                      '-infinity'::timestamptz) AND

                                                     coalesce(sites.date_of_last_service,
                                                              'infinity'::timestamptz)
                      ) x) y) _
                          GROUP BY pcod, datetime
                          ORDER BY pcod, datetime
                      ) z
                      GROUP BY pcod, date_trunc('hour', z.datetime)
                      ORDER BY pcod, date_trunc('hour', z.datetime)
                      ) _)]
              (Background on this error at: http://sqlalche.me/e/f405)

[greenape]

Wonder if this is also the case for SubscriberCallDurations? Fix is to use the special case for mode as in JoinedSpatialAggregate.