0

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

1 Answer 1

0

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
6
  • 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).
    – robbin
    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
    – Werter
    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.
    – robbin
    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. .
    – robbin
    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
    – Werter
    Aug 21, 2017 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.