SQL group data into an array

Question

I have this query which works:

select point_delivery_number, year(time - -120m) as year, month(time - -120m) - 1 as month, SUM(consumption) as value from "energy_datapoints"."formatted_raw"
WHERE point_delivery_number in ('AT523452345sadf345', 'AT2341234asdf5234452341243')
GROUP BY 1,2,3
ORDER BY year, month
LIMIT 24

It outputs this result:

I want to group this by its point_delivery_number

Result should be

point_delivery_number | year | data 
------------------------------------------------------
AT23...               | 2021 | [216.54, 202.77, 210.4]
------------------------------------------------------
At523..               | 2021 | [489.84, 423.6, ...]

I tried it with ARRAY_AGG

select point_delivery_number, year(time - -120m) as year, month(time - -120m) - 1 as month, ARRAY_AGG(SUM(consumption)) as data from "energy_datapoints"."formatted_raw"
WHERE point_delivery_number in ('AT523452345sadf345', 'AT2341234asdf5234452341243')
GROUP BY 1,2,3
ORDER BY year, month
LIMIT 24

But it says:

Cannot nest aggregations inside aggregation 'ARRAY_AGG'

I am using aws timestream database, so there are some limitations. Some functions do not even exist.

score 0 · Answer 1 · answered May 25 '22 at 11:49

I actually found it out by myself. I made a subquery:

select point_delivery_number, array_agg(value) as value from 
(select point_delivery_number, year(time - -120m) as year, month(time - -120m) - 1 as month, SUM(consumption) as value from "energy_datapoints"."formatted_raw"
WHERE point_delivery_number in ('AT523452345sadf345', 'AT2341234asdf5234452341243')
GROUP BY 1,2,3
ORDER BY year, month
LIMIT 24)
group by point_delivery_number

score 0 · Answer 2 · answered May 25 '22 at 11:55

The error is clear, you can't do aggregations inside the ARRAY_AGG function. One possible solution is to introduce a sub-query block, such as:

WITH sub_query AS (
  SELECT
    point_delivery_number,
    year(time - -120 m) as year,
    month(time - -120 m) - 1 as month,
    SUM(consumption) as value
  FROM
    "energy_datapoints"."formatted_raw"
  WHERE
    point_delivery_number in ('AT523452345sadf345', 'AT2341234asdf5234452341243')
  GROUP BY
    1, 2, 3
)

SELECT
  point_delivery_number,
  year,
  month,
  ARRAY_AGG(value)
FROM
  sub_query
GROUP BY
  1, 2, 3
ORDER BY
  2, 3

WITH statements are very useful to handle this kind of problem.

SQL group data into an array

2 Answers2