Select sum(*) from (select sum(*) ...) ... doesn't behave as I would expect

InfluxDB 2 Dashboards
1

Hi people!

First off, while I have some knowledge of working with time series database and grafana (although grafana is not involved here), I have only been messing around with InfluxDB on a personal basis. And everything is a bit new.

I have a bunch of very scarce data (maybe a couple points per month for a given tag in the measurement I’m working on).
And I’m trying to compute the sum of all these points, but the results are not what I would expect.

So let’s assume we have the following set of data:
insert values,t=0 value=1 1514786400000000000
insert values,t=0 value=2 1514959200000000000
insert values,t=1 value=1 1514959200000000000
insert values,t=0 value=3 1515045600000000000

select * from "values"
name: values
time                 tag value
----                 --- -----
2018-01-01T06:00:00Z 0   1
2018-01-03T06:00:00Z 0   2
2018-01-03T06:00:00Z 1   1
2018-01-04T06:00:00Z 0   3

Which is what we would expect.
Now the end result I’m basically trying to achieve is this:

2018-01-01T06:00:00Z 1
2018-01-02T06:00:00Z 1
2018-01-03T06:00:00Z 3
2018-01-04T06:00:00Z 4

Where, on a daily basis, you get the sum of everything, whatever the tags are.

My (obviously dumb) first try looked like this:

select sum(*) from "values" where time >= 1514786400000000000 and time <= 1515045600000000000 group by time(1d) fill(previous)
name: values
time                 sum_value
----                 ---------
2018-01-01T00:00:00Z 1
2018-01-02T00:00:00Z 1
2018-01-03T00:00:00Z 3
2018-01-04T00:00:00Z 3

This doesn’t work for obvious reasons.
Now, this gives me something closer to what I want:

select sum(*) from "values" where time >= 1514786400000000000 and time <= 1515045600000000000 group by t,time(1d) fill(previous)
name: values
tags: t=0
time                 sum_value
----                 ---------
2018-01-01T00:00:00Z 1
2018-01-02T00:00:00Z 1
2018-01-03T00:00:00Z 2
2018-01-04T00:00:00Z 3

name: values
tags: t=1
time                 sum_value
----                 ---------
2018-01-01T00:00:00Z
2018-01-02T00:00:00Z
2018-01-03T00:00:00Z 1
2018-01-04T00:00:00Z 1

This is valid. I keep previous values. I have one datapoint a day for each tag. And… I don’t seem to be able to add these properly…

select sum(*) from(select sum(*) from "values" where time >= 1514786400000000000 and time <= 1515045600000000000 group by t,time(1d) fill(previous)) where time >= 1514786400000000000 and time <= 1515045600000000000 group by time(1d)
name: values
time                 sum_sum_value
----                 -------------
2018-01-01T00:00:00Z 1
2018-01-02T00:00:00Z 1
2018-01-03T00:00:00Z 2
2018-01-04T00:00:00Z 2
2018-01-03T00:00:00Z 1
2018-01-04T00:00:00Z 4

While technically true, this is not really what I was expecting. I would expect group by time() to… group by time.
I tried to order by time but it didn’t seem to do anything.

Also:

select sum(*) from (select sum(*) from "values" where time >= 1514786400000000000 and time <= 1515045600000000000 group by t,time(1d) fill(previous))
name: values
time                 sum_sum_value
----                 -------------
1970-01-01T00:00:00Z 11

I feel like this should be 9. Not 11.
But that probably boils down to the same explanation as to why my previous requests don’t really do what I expect them to.

So yeah. I guess my question is can anyone provide me with a request that actually does what I want? And explain what I’m missing while we are at it :slight_smile:
If the answer is: you need to organize your data differently -> that’s fine too.

Thanks for the help!
Alexis

[EDIT] Forgot to mention I’m running InfluxDB version 1.4.2