Hi,
I have a use case where I would like to be able to do a SUM over a field but only include a reading if it has a reading in another field. More specifically, given the screenshot below, I would like to be able to make a query that adds 2 additional fields to the output (ColZ_BasedOnColX and ColZ_BasedOnColY). The ColZ_BasedOnColX field must contain a SUM of the readings in ColZ for which we have a corresponding reading in ColX. So, in this case ColZ_BasedOnColX should get the value 101.2 (10+6+2+32.6+2+32.6+10+6). Similarly, ColZ_BasedOnColY should get the value 105.2 because ColY also has readings in row 3 and 9.
Is this somehow possible?. I know this can be done by creating the 2 fields and and only insert a reading if the associated field has a value. However, this will take up quite a lot of disk space so I would much rather do it a run-time. Any suggestions?
@lrolsen You should be able to do this with subqueries. You can start with the following query as a base and then use a subquery to perform your additional math.
SELECT colx, coly, colz FROM measuerement WHERE colx > 0 AND coly > 0 AND colz > 0 AND time > now() - 1h
@jackzampolin I am not sure I follow. That query will filter the data down to only the rows that have values in all 3 fields. I am looking for a solution where the data going into the two additional fields is calculated independently between each field. So ColX would be a simple sum on ColX. The same goes for ColY. ColZ_BasedOnColX however is the sum of all rows in field Z except for row 3 and 9. ColZ_BasedOnColY is the sum of all rows in ColZ (since there are values for all rows in ColY). Finally, I would like it within a single statement as we usually have to deal with more than just 2 columns and having to have a separate statement for each field would then require quite a few statements.
Can you elaborate?
