Influxql query like an SQL union

I have 2 collectors - telegraf and MRTG. Telegraf collects SNMP metrics for my network devices and it collects the data in raw format; i.e. ifHCInOctets is collected as the counter it is, while MRTG collects the data already processed as a derivative. Because of this, I have stored MRTG data in a measurement called snmp, while Telegraf data is collected under measurement telegraf_snmp. The issue I have now is when it comes to charting the data using Grafana, there is no way to graphically display both queries as a single legend - grafana will display 2 legends, the tooltip will also show as 2 different values.

The solution I thought it would resolve this was to combine both measurements and generate a single output by running a query such as the one that follows:

SELECT sum(*) FROM ( SELECT mean("ifHCInOctets") as "mean" FROM "snmp" WHERE ("agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/) AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) ), (SELECT non_negative_derivative(mean("ifHCInOctets"),1s) AS "mean" FROM "telegraf_snmp" WHERE "agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/ AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) ) GROUP by time(1h)

But unfortunately this does not generate a single output but 2 outputs:

name: snmp
time                sum_mean
----                --------
1598968800000000000 1820567.1800000002
1598972400000000000 7382995.090000001
...
name: telegraf_snmp
time                sum_mean
----                --------
1598968800000000000 1796329.32
1598972400000000000 7403558.716666667
1598976000000000000 12098341.25
1598979600000000000 5369634.243333334
...

I also tried doing as follows which generated neither error nor output

SELECT "snmp"."mean" + "telegraf_snmp"."mean" FROM ( SELECT mean("ifHCInOctets") as "mean" FROM "snmp" WHERE ("agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/) AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) ), (SELECT non_negative_derivative(mean("ifHCInOctets"),1s) AS "mean" FROM "telegraf_snmp" WHERE "agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/ AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) )

Any ideas how to accomplish this via influxql? or even if telegraf has a means to generate derivative data rather than collecting and posting the raw counter value (though I get that it is the query language that would do the manipulation of the raw data)?

Hello @shija03,
I’m not sure that this is possible with InfluxQL. However, if you upgrade to 1.8 you can use Flux and the union() function.
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/union/