Aggregation queries results in too many rows

TimescaleDB and PostgreSQL
1

From the documentation

I expect the following query to return one result per bucket:

select time_bucket('5m', k.time) as time, sum( k.value) from test k
group by time
order by time asc;

but I get multiple results:

time sum
2023-09-20 09:35:00.000 0
2023-09-20 09:35:00.000 0

I created the tables as follows, this following the telegraf documentation

create table test (
"time" timestamp NOT NULL,
name varchar(20),
value int
);

SELECT create_hypertable('test','time',chunk_time_interval := INTERVAL '1 week', if_not_exists := true);

insert into test ("time", name, value) values ('2023-09-20 09:36:00', 'a', 0);

insert into test ("time", name, value) values ('2023-09-20 09:37:00', 'a', 0);
2

I modified your select query a bit.
Updated the alias name of time_bucket column and used same in groupby and orderby

select time_bucket('5m', k.time) as timee,
 sum( k.value) from test k
group by name,timee
order by timee asc;
2 Likes
3

Hi! @amit26feb gave the right answer :raised_hands:

That’s because you have the time column but you’re grouping by it.

I generally prefer to use like GROUP BY 1 ORDER BY 1 to guarantee I’m using the first expression which is the time_bucket(...) and not the time column.

1 Like