Query with multiple GROUP BY time

[kerstmansint]

Hi all,

Trying to figure this out for the last few days but couldn’t get it to work so wondering if anyone could help me out.

I have data from a sensor about my hourly energy usage (from Home Assistant through Influx). Only the sensor doesn’t have hourly data points, but it add’s each data point within a given hour and resets at the end to 0. So there are multiple data points per hour and the last one being the total value.

Now I can view the total hourly energy usage by SELECTING the max(value) and GROUP BY time(1h). This works fine. Only now I want the group the already grouped hourly data points again to each day and calculate the total sum so I know how much I’ve used each day. Only I have no idea how to group a query multiple times?

Would appreciate any help!

[kerstmansint]

Anyone who knows how the solve this?

[vmantzaridis]

I have a similar problem to solve. Did you ever find an answer to this?

[vmantzaridis]

Actually found a way using a nested query as per below, this takes the samples in 1h hour, calculates the average power consumption in 1h in terms of kWh and then I add up all the hours to make the daily

SELECT sum(“mean”) FROM (
SELECT mean(“value”)/1000 FROM “autogen”.“W” WHERE (“entity_id”::tag = ‘bottom_rack_power_consumption’) AND $timeFilter GROUP BY time(1h) fill(null)) WHERE $timeFilter GROUP BY time(1d) fill(null)