# How to perform AVG, arithmetic operations in MySQL query

I am trying to perform some arithmetic operations and `avg` in single query, e.g I had two column data in a table which looks like this

``````Temp            value         value2

1401631200      -85.5582      6.87686
1401631201      -85.266       6.54354
1401631202      -83.266       6.9174344
1401631203      -84.1243      6.900333
.
.
.
.
.
1401632400      -83.43532     6.3335622
``````

So I perform `AVG` by using this query statement and it works perfectly fine and produces the average value. and SQL statement is

``````SELECT avg(value) as value_avg1 FROM kami_all_dat WHERE usec >= 1401631200 AND
usec <= 1401632400
``````

and for average of value2 I can use this statement

``````SELECT avg(value2) as value_avg2 FROM kami_all_dat WHERE usec >= 1401631200 AND
usec <= 1401632400
``````

I am trying to perform some arithmetic operations after getting this average value on the fly in query. my formula looks like:

``````final_val = value_avg1 + (5.67 * 10^(-8) * (value2_avg + 273.14)^4)
``````

I am trying to achieve like this, for example

``````SELECT (avg(value) as Average FROM kami_all_dat WHERE usec >= 1401631200
AND usec <= 1401632400) + {arithmetic operation} * SELECT (avg(value2) as
value_avg1 FROM kami_all_dat WHERE usec >= 1401631200 AND usec <= 1401632400) +
{arithmetic operations}
``````

I am trying to achieve the single value as `final_val` and I am trying to perform all these operation in a single query. But I am facing difficulty to do these arithmetic operations as mention in `final_val` in a single query as well as doing this average values calculation in single query.

I am beginner to database technology. Any help or guidance will be greatly appreciated. Thanks

You can simply use the arithmetic operators in the first SELECT

``````SELECT
AVG(value) as value_avg1,
AVG(value2) as value_avg2,
AVG(value) + (5.67E-8 * POWER(AVG(value2) + 273.14, 4)) as final_value
FROM kami_all_dat
WHERE usec >= 1401631200 AND usec <= 1401632400
``````
• thanks, but I also want to include........... [SELECT avg(value) as value_avg1 FROM kami_all_dat WHERE usec >= 1401631200 AND usec <= 1401632400] and [SELECT avg(value2) as value_avg2 FROM kami_all_dat WHERE usec >= 1401631200 AND usec <= 1401632400] in the query .... in place of AVG(value) and AVG(value2). Aug 21, 2017 at 14:29
• You can add those as columns: SELECT avg(value) as value_avg1, avg(value2) as value_avg2, AVG(value) + (0.0223 * POWER(AVG(value2) + 273.14, 2)) as final_value FROM kami_all_dat WHERE usec >= 1401631200 AND usec <= 1401632400 Aug 21, 2017 at 14:34
• thanks for help. Now I can perform my arithmetic calculations and avg values in single query. Although I am getting some wrong result, but maybe that has something to do with my query formula values or power. If I didn't figure out correctly, then I will post a comment for guidance. Aug 21, 2017 at 15:05
• there is something wrong with the brackets operation. e.g My column value returns me average of -84.339 and value2 return me 6.739161, and after implementing with the help of query , it should return me 263.56 as final_val.. , but it return me 6.417783... . I guess the brackets are not working properly. Can you give it a look cox i try my best to remove the problem but unable to do it. .I had change the value and value2 data in question also for better understanding. thanks for help. . Aug 21, 2017 at 15:57
• Adjusted my answer to reflect the changes you made in the question. When run on the sample data you provide. I get: value_avg1 = -84.3, vaue_avg2 = 6.7 and final_value = 263.5 Aug 21, 2017 at 16:08