# Kapacitor and subqueries? **URL:** \ **Category:** Systems\ **Tags:** influxdb, kapacitor\ **Created:** [March 9, 2017, 3:41pm UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175 "2017-03-09T15:41:21Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Marijus](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/marijus/32/30_2.png) [@Marijus](https://community.influxdata.com/u/Marijus)\ **Post date:** [March 9, 2017, 3:41pm UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/1 "2017-03-09T15:41:21Z")
Hello, I am trying to backfill some data using subquery with Kapacitor. The TICK script looks like this: ``` var test = batch |query(''' SELECT SUM("mean") from (SELECT mean("value") FROM "collectd"."default".value" WHERE "host" =~ /^(..).[0-9]*.example.com/ AND "type_instance" = 'default_interface_rx') ''') .period(1h) .every(1h) .groupBy(time(1h), *) .align() |delete() .tag('type') |influxDBOut() .database('history') .measurement('test') ``` After defining the script I am trying to record the batch of the past day with: `kapacitor record batch -task one_by_one -past 1d` , but I am getting an error: > failed to parse InfluxQL query: found (, expected identifier at line 2, char 33 Looks like there is an error in the query itself? Any help would be appreciated. Marijus ---
**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:** [March 9, 2017, 5:40pm UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/2 "2017-03-09T17:40:08Z")
@Marijus Does that query return any data if you run it against your InfluxDB instance? It is giving me a parse error locally. Also can you please include your `influxd` and `kapacitor` versions? ---
**Author:** ![nathaniel](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/nathaniel/32/9_2.png) [@nathaniel](https://community.influxdata.com/u/nathaniel)\ **Post date:** [March 9, 2017, 6:12pm UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/3 "2017-03-09T18:12:25Z")
Kapacitor does not currently (v1.2) support subqueries. Support will come in a later release. ---
**Author:** ![Marijus](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/marijus/32/30_2.png) [@Marijus](https://community.influxdata.com/u/Marijus)\ **Post date:** [March 9, 2017, 7:09pm UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/4 "2017-03-09T19:09:57Z")
@jackzampolin, sure, I am using: > InfluxDB 1.2.0-1 > Kapacitor 1.2.0-1 With the query I can get the results: > SELECT SUM(“mean”) from (SELECT mean(“value”) FROM “collectd”.“default”.“value” WHERE “host” =~ /^(…).[0-9]\*.example.com/ AND “type\_instance” = ‘default\_interface\_rx’ AND time \> now() - 1h GROUP BY time(1h)) > name: value > time sum > > * * * > > 0 6.187313478441984e+13 ---
**Author:** ![Marijus](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/marijus/32/30_2.png) [@Marijus](https://community.influxdata.com/u/Marijus)\ **Post date:** [March 9, 2017, 7:11pm UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/5 "2017-03-09T19:11:28Z")
@nathaniel, thank You for the update! Is there an easy way to sum the value in this case in other way? ---
**Author:** ![nathaniel](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/nathaniel/32/9_2.png) [@nathaniel](https://community.influxdata.com/u/nathaniel)\ **Post date:** [March 9, 2017, 8:00pm UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/6 "2017-03-09T20:00:57Z")
Yes, Make the batch query the inner query and then perform the suming operation in Kapacitor with the |sum() node. ``` var test = batch |query(''' SELECT mean("value") FROM "collectd"."default".value" WHERE "host" =~ /^(..).[0-9]*.example.com/ AND "type_instance" = 'default_interface_rx' ''') .period(1h) .every(1h) .groupBy(time(1h), *) .align() |sum('mean') |delete() .tag('type') |influxDBOut() .database('history') .measurement('test') ``` Something along those lines ---
**Author:** ![Marijus](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/marijus/32/30_2.png) [@Marijus](https://community.influxdata.com/u/Marijus)\ **Post date:** [March 10, 2017, 10:27am UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/7 "2017-03-10T10:27:58Z")
I tried it, but this solution doesn’t work for me either. What I want to achieve is to have the sum of default\_interface\_rx of all hosts that falls into this regular expression: /^(…).[0-9]\*.example.com/. Instead, I am still getting ``` select * from test where time > now() - 2h name: test time host sum type_instance ... 2017-03-10T09:00:00Z xxx.example.com 7.513598842337522e+13 default_interface_rx 2017-03-10T09:00:00Z xxx.example.com 7.747260959693897e+13 default_interface_rx 2017-03-10T09:00:00Z xxx.example.com 6.372869431513417e+11 default_interface_rx 2017-03-10T09:00:00Z xxx.example.com 2.4050401161566e+12 default_interface_rx ... ``` Looks like I am grouping incorrectly, or how the sum node works in this situation? ---
**Author:** ![Adrian\_Bravo](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/adrian_bravo/32/3151_2.png) [@Adrian\_Bravo](https://community.influxdata.com/u/Adrian_Bravo)\ **Post date:** [July 21, 2020, 11:46am UTC](https://community.influxdata.com/t/kapacitor-and-subqueries/175/8 "2020-07-21T11:46:53Z")
I am having a similar issue, i need to put a sum query to an anomalie ml model, and i cant execute the entire query, and i dont know how to do it… the exactly query i need to parse and works me in influxdb is: SELECT sum(brs) FROM(SELECT mean(“batch\_requests”) as brs FROM nagios\_mssqlserver.“autogen”.“batch\_requests” WHERE time \> now() - 6h AND (“host\_name”=‘demunsql1702.production.ecom.techdata’ OR “host\_name”=‘demunsql1707.production.ecom.techdata’ OR “host\_name”=‘demunsql1708.production.ecom.techdata’ OR “host\_name”=‘demunsql1709.production.ecom.techdata’ OR “host\_name”=‘demunsql1710.production.ecom.techdata’) GROUP BY host\_name, time(10m) ) WHERE time \> now() - 6h GROUP BY time(10m) Anybody can help me please? i dont know to much about batch tick scripts… Thanks in advice