# CQ that matches SELECT INTO not working **URL:** \ **Category:** Kapacitor\ **Tags:** influxdb\ **Created:** [July 4, 2017, 10:58pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508 "2017-07-04T22:58:38Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![russ](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/russ/32/3844_2.png) [@russ](https://community.influxdata.com/u/russ)\ **Post date:** [July 4, 2017, 10:58pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/1 "2017-07-04T22:58:39Z")
I am trying to make a continuous query (CQ) work to match an `SELECT INTO` query that works correctly. Specifically, I’m trying to make a CQ to accurately calculate the send and recv bandwidths from the `net` input in such a way that aggregate queries/visualizations don’t change based in the size of the aggregation window size. To backfill the data, this `SELECT` works perfectly (formatted for readability): ```sql SELECT 8*non_negative_derivative(max("bytes_recv"),1s) AS rx_rate_bps ,8*non_negative_derivative(max("bytes_sent"),1s) AS tx_rate_bps INTO "net_bandwidth_cq" FROM "net" WHERE time > now() - 5d AND interface = 'eth0' GROUP BY time(1s), host, * ``` The matching CQ that I make to keep this up to date is done this way: ```sql CREATE CONTINUOUS QUERY "cq_net_bandwidth" ON telegraf BEGIN SELECT 8*non_negative_derivative(max("bytes_recv"),1s) AS rx_rate_bps ,8*non_negative_derivative(max("bytes_sent"),1s) AS tx_rate_bps INTO "net_bandwidth_cq" FROM "net" WHERE interface = "eth0" GROUP BY time(1s), host, * END ``` This seems to create ok, and CQ logging (in syslog) seems to indicate that it does run successfully, but it definitely does _not_ add to the same measurement that was created with the `SELECT INTO` above, nor can I figure out where the CQ results end up. Here is how it registers itself: ```auto > SHOW CONTINUOUS QUERIES name: _internal name query ---- ----- name: telegraf name query ---- ----- cq_net_bandwidth CREATE CONTINUOUS QUERY cq_net_bandwidth ON telegraf BEGIN SELECT 8 * non_negative_derivative(max(bytes_recv), 1s) AS rx_rate_bps, 8 * non_negative_derivative(max(bytes_sent), 1s) AS tx_rate_bps INTO telegraf.autogen.net_bandwidth_cq FROM telegraf.autogen.net WHERE interface = eth0 GROUP BY time(1s), host, * END ``` What am I doing wrong here? Why doesn’t this work? ---
**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:** [July 5, 2017, 4:54pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/2 "2017-07-05T16:54:13Z")
@russ I would imagine with the CQ running every second there may not be data for that second in the database. I would suggest looking into adding an [`EVERY ... FOR ... `](https://docs.influxdata.com/influxdb/v1.2/query_language/continuous_queries/#advanced-syntax) clause to your CQ to make it run for a long time period less frequently. ---
**Author:** ![russ](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/russ/32/3844_2.png) [@russ](https://community.influxdata.com/u/russ)\ **Post date:** [July 5, 2017, 5:50pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/3 "2017-07-05T17:50:03Z")
Thanks, @jackzampolin. I will look into the `EVERY ... FOR ...` clause. However, I’ve recently figured ou that if I drop the `WHERE` clause from the query it works perfectly. I’m actually not sure how the derivative function is working properly for all the devices… but it is. ie: I can filter the CQ result on `interface = 'eth0'` and it gives me the numbers I expect. ---
**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:** [July 5, 2017, 5:50pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/4 "2017-07-05T17:50:58Z")
@russ Thats because you are `GROUP BY *`! This will separate the series and `derivative()` will work on each one. ---
**Author:** ![russ](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/russ/32/3844_2.png) [@russ](https://community.influxdata.com/u/russ)\ **Post date:** [July 5, 2017, 7:11pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/5 "2017-07-05T19:11:39Z")
Getting the independent derivatives for all interfaces is _much_ better than filtering on `eth0` only! Now that I know that the `GROUP BY *` does this series splitting, I added it to the backfilling `INSERT` query as well. The `interface = eth0` filter was to get around the smushing of interfaces that was happening, and the `GROUP BY *` result is MUCH more satisfying. Thanks! This is not relevant any more, but… any idea why the `WHERE` clause would have wrecked the query, then? With it in place there was no data being added to the targeted `cq_net_bandwidth` measurement. Also, after the `RESAMPLE EVERY .. FOR ..` tip, I’m now using this query, which seems to be working very well now: ```auto CREATE CONTINUOUS QUERY "cq_net_bandwidth" ON "telegraf" RESAMPLE EVERY 60s for 60s BEGIN SELECT 8*non_negative_derivative(max("bytes_recv"),1s) AS "rx_rate_bps" ,8*non_negative_derivative(max("bytes_sent"),1s) AS "tx_rate_bps" INTO "net_bandwidth_cq" FROM "net" GROUP BY time(10s), host, * END ``` Now when I view a chart of the max() of this data in grafana, it now behaves rationally, with charted (peak) values being independent of zoom scaling. This was the end objective, so… woohoo! Thanks! ---
**Author:** ![russ](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/russ/32/3844_2.png) [@russ](https://community.influxdata.com/u/russ)\ **Post date:** [July 5, 2017, 7:15pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/6 "2017-07-05T19:15:25Z")
Actually, I see I left `host` in the `GROUP BY` clause. It is working as-is, but I’m wondering if (since `host` is a tag) the `GROUP BY *` bit causes the `host` part to be ignored. Is it needed, or can it be dropped? ---
**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:** [July 5, 2017, 9:06pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/7 "2017-07-05T21:06:58Z")
@russ You can pull out the `host` there. It is ignored in the current query. Glad you were able to get that working! ---
**Author:** ![russ](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/russ/32/3844_2.png) [@russ](https://community.influxdata.com/u/russ)\ **Post date:** [July 5, 2017, 11:38pm UTC](https://community.influxdata.com/t/cq-that-matches-select-into-not-working/1508/8 "2017-07-05T23:38:25Z")
Thanks… it makes sense to me now that it is ignored. For anyone else who lands here, the final continuous query is: ```auto CREATE CONTINUOUS QUERY "cq_net_bandwidth" ON "telegraf" RESAMPLE EVERY 60s for 60s BEGIN SELECT 8*non_negative_derivative(max("bytes_recv"),1s) AS "rx_rate_bps" ,8*non_negative_derivative(max("bytes_sent"),1s) AS "tx_rate_bps" INTO "net_bandwidth_cq" FROM "net" GROUP BY time(10s), * END ```