# Help writing continuous query subtracting two series **URL:** \ **Category:** Systems\ **Created:** [May 23, 2017, 1:28am UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023 "2017-05-23T01:28:31Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![adamalli](https://avatars.discourse-cdn.com/v4/letter/a/b3f665/32.png) [@adamalli](https://community.influxdata.com/u/adamalli)\ **Post date:** [May 23, 2017, 1:28am UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023/1 "2017-05-23T01:28:31Z")
I am new to influxdb and writing queries and need some help. I would like to setup a continuous query that subtracts two values with the result being stored. I am not very familiar with query language so that is where I am struggling. What I want to do is subtract `SELECT * FROM "Home" WHERE "topic" = '/house/power/main'` from `SELECT * FROM "Home" WHERE "topic" = '/house/power/solar'` and store in a “topic” called “/house/power/netmain” at the same time interval the rest of the data is being collected. I am using influxdb v1.2 and my data is setup as shown below. This data is going to Grafana so if there is a way to do this there without the continuous query that would work too. `time host topic value 2017-05-17T01:31:49.303162625Z "AllingtonServ" "/house/power/main" 2323.419 2017-05-17T01:32:20.30254561Z "AllingtonServ" "/house/power/main" 2304 2017-05-17T01:32:51.308210601Z "AllingtonServ" "/house/power/main" 2322.935 2017-05-17T01:31:49.303162625Z "AllingtonServ" "/house/power/solar" -1250 2017-05-17T01:32:20.30254561Z "AllingtonServ" "/house/power/solar" -1240 2017-05-17T01:32:51.308210601Z "AllingtonServ" "/house/power/solar" -1238` ---
**Author:** ![jackzampolin](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jackzampolin/32/5_2.png) [@jackzampolin](https://community.influxdata.com/u/jackzampolin)\ **Post date:** [May 23, 2017, 6:22pm UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023/2 "2017-05-23T18:22:37Z")
@adamalli In order to do math on data like you are describing you need to write that data as `field`s. Your writes in that case would look as follows: ```auto AllingtonServ,tag1=tagValue house_power_main= 2323.419,house_power_solar=-1250 TS AllingtonServ,tag1=tagValue house_power_main= 2304,house_power_solar=-1240 TS AllingtonServ,tag1=tagValue house_power_main= 2322.935,house_power_solar=-1238 TS ``` Then you would use a [continuous query](https://docs.influxdata.com/influxdb/v1.2/query_language/spec/#create-continuous-query) similar to the below: ```auto CREATE CONTINUOUS QUERY non_solar_generation ON data BEGIN SELECT (mean(house_power_main) - mean(house_power_solar)) AS derived INTO AllingtonServDerived FROM AllingtonServ GROUP BY time(30s) END ``` You could also skip the CQ and just run the math on the fly with the inner `SELECT` statement there. ---
**Author:** ![adamalli](https://avatars.discourse-cdn.com/v4/letter/a/b3f665/32.png) [@adamalli](https://community.influxdata.com/u/adamalli)\ **Post date:** [May 23, 2017, 10:56pm UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023/3 "2017-05-23T22:56:09Z")
Unfortunately my data is coming from individual MQTT subscriptions using telegraf to input into influxdb. My setup has 96 subscriptions, 32 in each topic. With in each topic the channels are named like main, solar, liv lights, etc… My telegraf setup is as below. topics = [ "/house/volts/#", "/house/power/#", "/house/energy\_total/#", "/house/dif\_energy/#", ] It sounds like the way my data is being collected it is not going to work for the format you suggest above. ---
**Author:** ![jackzampolin](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jackzampolin/32/5_2.png) [@jackzampolin](https://community.influxdata.com/u/jackzampolin)\ **Post date:** [May 24, 2017, 12:14am UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023/4 "2017-05-24T00:14:51Z")
@adamalli So I’ve been trying to do this with `INTO` queries for a while but I don’t know if I can. It looks like you are writing points into `MQTT` like so `AllingtonServ value=1 TS` with no other metadata and then you are relying on the `MQTT` topic to provide context. This is not a recommended way to use the database. Your particular sin would be [stuffing an individual tag with too much metadata](https://docs.influxdata.com/influxdb/v1.2/concepts/schema_and_data_layout/#don-t-put-more-than-one-piece-of-information-in-one-tag). I would assume you probably have a `HouseId=XXXX` tag as well. At time of write to `MQTT` in your client you have all of the information needed to write the field names. You can still write them as individual points (`AllingtonServ,HouseId=XXXX power_main=1939 TS`) and have the database take care of them as long as the timestamps are set client side and the metadata all lines up. Is it possible to make that change to your clients? If not you would likely need to use the [Kapacitor](https://github.com/influxdata/kapacitor) [`flatten` node](https://docs.influxdata.com/kapacitor/v1.3//nodes/where_node/#flatten) and the [`dropOriginalFieldName` node](https://docs.influxdata.com/kapacitor/v1.3//nodes/flatten_node/#droporiginalfieldname) to make the data look as I described: ```auto stream |from() ... |flatten() .on(topic) .dropOriginalFieldName(TRUE) ``` The reason for this is that InfluxDB only allows math between fields in the same measurement. ---
**Author:** ![adamalli](https://avatars.discourse-cdn.com/v4/letter/a/b3f665/32.png) [@adamalli](https://community.influxdata.com/u/adamalli)\ **Post date:** [May 24, 2017, 2:10am UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023/5 "2017-05-24T02:10:23Z")
Well that all makes sense. Some background for you. I have a power monitoring device called Greeneye monitor that monitors the power of every circuit in my house. I then use a python script called [btmon.py](https://github.com/BenK22/mtools/blob/dc9191c46a3df6bbd1b8ae9de4cc35c66371c667/bin/btmon.py) that was written to upload data to various online services. One of the services it provides is to publish to a MQTT server. It could probably be modified to have the better format you state above, but I am not a programmer so I will have to see if the original developer could make those changes. The python script does have the ability to input data directly to influxdb in the format you mention, but I wanted to use MQTT for some other reasons. ---
**Author:** ![jackzampolin](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jackzampolin/32/5_2.png) [@jackzampolin](https://community.influxdata.com/u/jackzampolin)\ **Post date:** [May 24, 2017, 8:48pm UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023/6 "2017-05-24T20:48:10Z")
@adamalli Well you could always use [`telegraf`](https://github.com/influxdata/telegraf/tree/master/plugins/inputs/http_listener) as your metrics batcher and forwarder. That InfluxDB output would be idea. ---
**Author:** ![sorriso93](https://avatars.discourse-cdn.com/v4/letter/s/6de8d8/32.png) [@sorriso93](https://community.influxdata.com/u/sorriso93)\ **Post date:** [May 30, 2020, 1:47pm UTC](https://community.influxdata.com/t/help-writing-continuous-query-subtracting-two-series/1023/7 "2020-05-30T13:47:42Z")
Hello I have the same problem I think. The single queries are ok but the combined one give no result, any suggestion? SELECT (non\_negative\_derivative(mean(“POWERPANNELLI”), 120s )- non\_negative\_derivative(mean(“POWERCASA”), 120s )) AS “Consumo” FROM ( SELECT mean(“ENERGY\_Power”) AS “POWERCASA” FROM “telegraf”.“autogen”.“CASA” WHERE time \> :dashboardTime: AND time \< :upperDashboardTime: GROUP BY time(1m) ), ( SELECT mean(“ENERGY\_Power”) AS “POWERPANNELLI” FROM “telegraf”.“autogen”.“Pannelli” WHERE time \> :dashboardTime: AND time \< :upperDashboardTime: GROUP BY time(1m) ) GROUP BY time(1m) fill(null)