# Using mathematical operators with MySQL

Hi there,

I am new to Grafana (and very basic with MySQL). I managed to set up my “normal” queries and graphs, but the following is a little over my head.

Goal is, to get the average temperature value form across different tables. Each table consists of two data columns, a date (e.g. 2018-11-19 16:00:00) and the temperature value. For example (dates are shortened):

Time Value
16:00 20.01
16:03 21.10
16:04 20.51
16:11 19.05

The data sources are different sensor. A new value is written every time, a sensor reports a new value. There is no synchronised update-interval. So the second table might look like:

Time Value
16:01 21.00
16:04 22.00
16:08 20.00
16:14 23.00

If possible, Grafana should generate 8 data points out of this (or seven, as 16:04 shows up twice):

Time Value
16:00 20.01 (optional, as occurring only once for each data series)
16:01 (20.01+21.00)/2
16:03 (21.01+21.00)/2
16:04 (20.51+22.00)/2
16:08 (20.51+20.00)/2
…etc.

I hope I explained the understandable. Help would be appreciated, as every try to add the values ended in a syntax error.

Thanks

Alex

the first thing is that I suppose you have both tables in the same database,
and according to the data you provide, 8 data are generated as shown in the following image.

but it will only be one data per minute, that is, every minute the sensor data will be averaged.

this is the query

SELECT
UNIX_TIMESTAMP(date_format(time, ‘%Y-%m-%d %H:%i’)) as time_sec,
avg(value) as value,
“avg sensors” as metric
from
(select sensor1.time,sensor1.value from sensor1
union
select sensor2.time,sensor2.value from sensor2
)x
WHERE \$__timeFilter(time)
GROUP BY date_format(time, ‘%Y-%m-%d %H:%i’)
ORDER BY time ASC

But for some reason, this won’t work for me. I changed the table names and played around a bit, but can’t figure out the error:

Greets

I managed to get rid of the syntax error. The code is:
SELECT
UNIX_TIMESTAMP(Time) as time_sec,
avg(Value) as value,
“Durchschnitt” as metric
from
(select Item26.Time,Item26.Value from Item26
union
select Item29.Time,Item29.Value from Item29
)x
WHERE \$__timeFilter(Time)
GROUP BY Time
ORDER BY Time ASC

My result is same as danielgonzalez’. But this is only working, if the data of the two series has the exact same timestamp. I need a function where the last available datapoint is taken, if there is no value for the current time (or interpolate a new value from two values of one time series, but this might be overkill).

if you change the query, the result it’s not the same