1

I'm totally new here, I tried to find solution and I found this:
Influxdb and Grafana combine multiple SELECT

but this works fine if we have different fields from the same measurement, but this does not work (for me) when adding values from different measurements. What am I doing wrong here? Could you please take a look into this? Updated query - with proper interval - still does not work.

SELECT ( sum("valFromA") + sum("valFromB") )  
AS "addedVal"   
FROM (  
    SELECT mean("valueA") AS "valFromA"  
    FROM "DSA"   
    WHERE "hostname" = 'host' AND $timeFilter GROUP BY time(15m) fill(null)  
),  
(      
    SELECT mean("valueB") AS "valFromB"  
    FROM "DSB"   
    WHERE "hostname" = 'host' AND $timeFilter GROUP BY time(15m) fill(null)  
)  
GROUP BY time(15m) fill(null)  

Nothing is drawn in this case. This also show 2 counters under graph DSA.addedval DSB.addedval

BTW - when i split this queries to separate ones - it returns data. Combined as above shows empty graph. e.g.

Single select is ok:

SELECT ( sum("valFromA") )  
AS "addedVal"   
FROM (  
     SELECT mean("valueA") AS "valFromA"  
     FROM "DSA"   
     WHERE "hostname" = 'host' AND $timeFilter GROUP BY time(15m) fill(null)  
)
GROUP BY time(15m) fill(null)  

Grafana 6.4.4 + Influx 5.1.0 Do I need to do something in Influx for this? I'm asking because I've access to grafana only, but I may ask our administrators.

UPDATE: I really tried to put this into proposed "select" by Akina, but I didn't succeed. I don't want to paste here my trials, because grafana does not understand clause UNION ALL. So maybe it is possible but in influx not directly from visualization (graph) query definition.

This is what I'm trying to do:

SELECT ( (sum("opt12") / sum("dst45")) * 100 )
AS "Ratio" 
FROM (
    SELECT mean("ratioopt12") AS "opt12"
    FROM "ottb.gen.11" 
    WHERE "hostname" = "serv1"  AND $timeFilter GROUP BY time(15m) fill(null)
),
( 
    SELECT mean("ratiodst45") AS "dst45"
    FROM "ottb.gen.14" 
    WHERE "hostname" = "serv1" AND $timeFilter GROUP BY time(15m) fill(null)
)
GROUP BY time(15m) fill(null)

Really have no idea what should I replace "field" with in this case, and what about group by time(15m) here?

SELECT sum(summ), field FROM (
   SELECT sum(val_1), field AS summ FROM tableA GROUP BY field 
   UNION ALL 
   SELECT sum(val_2), field FROM tableB GROUP BY field
) GROUP BY field    

Any help would be appreciated.

Regards,
Mike

Mike
  • 11
  • 1
  • 3
  • Grouping by some time expression gives you separate sums - one sum for each separate time expression value. But you do NOT select this time expression value - so there is no way to understand what sum to what time is matched (in general the order of output records is random if no explicit ordering). So the result makes no sense. – Akina Dec 11 '19 at 06:51
  • Updated query (with interval) – Mike Dec 11 '19 at 11:24
  • Nothing changed. – Akina Dec 11 '19 at 11:30
  • Thank you. I updated my question with proper interval value - it's the same for both measurements - still does not work. – Mike Dec 11 '19 at 11:30
  • Use select sum(summ), field from (select sum(val_1), field as summ from tableA group by field union all select sum(val_2), field from tableB group by field) group by field. – Akina Dec 11 '19 at 11:32
  • Thanks, are you talking about influx query? If so, I don't have rights to influx. Grafana does not understand field. I receive parsing error: error parsing query: found FIELD, expected identifier, string, number, bool at line 1, char 22 – Mike Dec 11 '19 at 11:56
  • I was hoping you would replace my placeholders with your real names... – Akina Dec 11 '19 at 12:01
  • Thanks, I tried to - but I assumed that field in this case is keyword - because you have "as" in first query and not in second query. Field names in both queries are different. Could you please modify my example from question into your proposal? I would be very grateful for your help :) Thanks in advance. – Mike Dec 11 '19 at 14:15
  • Akina - I really appreciate your answer, but I have no idea what should I replace "field" with when I have different fields in each measurements. Could you please help me with this? I'm trying to do this from grafana panel , not from influx where I have no direct access. In my trials grafana didn't understand UNION ALL (ALL) and expected ';' instead of ALL, but maybe I did something wrong. – Mike Dec 18 '19 at 14:02

0 Answers0