Find average value for each hour interval in a certain time period

I have a database with the following columns: time, device, metric, value. For every minute, this database collects the value for the respective device/metric combination.

I'm trying to write code that will give me the average value over each hour (so for example, rather than giving me 60 values for 5-6 pm, it averages all those values between 5-6 pm and outputs 1 value).

So far, I have this code:

SELECT
  TIME_FLOOR(__time, 'PT1h') AS "__time_time_floor",
  "value", COUNT(*) AS "Count"
FROM "database"
WHERE "__time" >= CURRENT_TIMESTAMP - INTERVAL '1' DAY AND "device" = 'device_1'AND "metric"='metric_1'
GROUP BY 1, 2
ORDER BY "__time_time_floor" DESC

This code outputs the value at every hour (4,5,6 pm...) for the device/metric (I think). But, I want the average value over each hour interval, as I said above. Not sure how to do this. I tried using "AVG" and "SUM" functions but I get errors.

Solution

Hmmm . . . I'm not familiar with all the functions, but it seems you want the AVG() aggregation function:

SELECT TIME_FLOOR(__time, 'PT1h') AS "__time_time_floor",
       AVG("value"), COUNT(*) AS "Count"
FROM "database"
WHERE "__time" >= CURRENT_TIMESTAMP - INTERVAL '1' DAY AND "device" = 'device_1'AND
      "metric"='metric_1'
GROUP BY 1
ORDER BY "__time_time_floor" DESC;

Basically, this removes VALUE from the GROUP BY.