Outer group by without aggregation in subquery

#1

Hi there.

I found a (workaround?/)solution to get the positive value change per day.

I have 2 databases:

  • test
  • test2

These are my raw values in database test

> select value from "autogen"."pulsethingy"
name: pulsethingy
time                value
----                -----
1521370800000000000 50001
1521388800000000000 50002
1521406800000000000 50003
1521424800000000000 50004
1521442800000000000 50005
1521460800000000000 50006
1521478800000000000 50007
1521496800000000000 50008
1521514800000000000 50009
1521532800000000000 50010
1521550800000000000 1
1521568800000000000 2
1521586800000000000 3
1521604800000000000 4
1521622800000000000 5
1521640800000000000 6
1521658800000000000 7
1521676800000000000 8

I calculate the positive value change over time, and store it in database test2

> select non_negative_difference(value) into "test2"."autogen"."pulsethingy" from "autogen"."pulsethingy"
name: pulsethingy
time                non_negative_difference
----                -----------------------
1521388800000000000 1
1521406800000000000 1
1521424800000000000 1
1521442800000000000 1
1521460800000000000 1
1521478800000000000 1
1521496800000000000 1
1521514800000000000 1
1521532800000000000 1
1521568800000000000 1
1521586800000000000 1
1521604800000000000 1
1521622800000000000 1
1521640800000000000 1
1521658800000000000 1
1521676800000000000 1

Then I group it by day

> use test2
Using database test2

> select sum("non_negative_difference") from "autogen"."pulsethingy" group by time(1d)
name: pulsethingy
time                sum
----                ---
1521331200000000000 2
1521417600000000000 5
1521504000000000000 4
1521590400000000000 4
1521676800000000000 1

Which gives me the positive change per day.

It does not seem possible to use one query (with a subquery) to get to this same result.

> use test
Using database test

> select sum("non_negative_difference") from (select non_negative_difference(value) from "autogen"."pulsethingy") group by time(1d)
ERR: aggregate function required inside the call to non_negative_difference

I do not understand why I have to store the result of the first query in a separate measurement before I can process it further. Is this workaround something I can avoid?

Query the postive change per day