|
Subject:
|
Calculation with two averages
|
|
Posted By:
|
mushu
|
Post Date:
|
9/22/2004 11:18:56 AM
|
Hi, could someone help me, please?
I have to get two averages, and to do a calculation with them.
Average 1:
select avg(value) from
(select
datetime,
(select value from history
where tagname = 'IS_Press_Pulp_H2O'
and datetime = h.datetime) value
from history h
where tagname = 'IS_Press_Pulp_H2O_Sample' and
wwretrievalmode = 'delta' and
wwrowcount = 0 and
datetime > convert(varchar(10), getdate(), 121) + ' 00:00:00'
and
datetime < convert(varchar(10), dateadd(dd, 1, getdate()), 121) + ' 00:00:00') history
Average 2:
select avg(value) from
(select
datetime,
(select value from history
where tagname = 'IS_Feed_Wt_Pol'
and datetime = h.datetime) value
from history h
where tagname = 'IS_Feed_Wt_Pol_Ref' and
wwretrievalmode = 'delta' and
wwrowcount = 0 and
datetime > convert(varchar(10), getdate(), 121) + ' 00:00:00'
and
datetime < convert(varchar(10), dateadd(dd, 1, getdate()), 121) + ' 00:00:00') history
and with these two results:
(Average1 / Average2) * 100
Do you know how could I join it?
Thanks,
Mushu
|
|
Reply By:
|
bmains
|
Reply Date:
|
9/22/2004 11:34:31 AM
|
Did you try:
If you are in a stored proc, did you try to select the results to variables, and then calculate the variables?
Brian
|
|
Reply By:
|
Justin Cave
|
Reply Date:
|
9/22/2004 11:56:40 AM
|
What database are you using? There may be a more efficient way with database-specific syntax, but if you have two one-row subqueries, you should be able to do
SELECT query1.avg1 / query2.avg2 * 100
FROM (SELECT AVG(value) avg1
FROM <<rest of first query>>) query1,
(SELECT AVG(value) avg2
FROM <<rest of second query>>) query2
This will do a Cartesian join. Normally, this is very bad, but since you will have just 1 row, a 1x1 Cartesian join is still just 1 row, so it should be quite fast.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
|
|
Reply By:
|
mushu
|
Reply Date:
|
9/23/2004 12:01:27 PM
|
Thank you bmains, and Justin; I have not tryed you code yet, I have been on site, I will let you know if it works.
bmains, I am new in SQL, so I don't know very well how to use the procedures.
Thanks,
Mushu
|
|
Reply By:
|
mushu
|
Reply Date:
|
9/28/2004 6:48:13 AM
|
just perfect, thank you. Sorry for being so late, but I couldn't test it earlier.
It works perfect. Maybe I will try to find how to use procedures, to do it more efficient.
Thanks,
Mushu
|
|
Reply By:
|
bmains
|
Reply Date:
|
9/28/2004 6:56:49 AM
|
FYI, what I recommended was to do:
declare @val1 float, @val2 float
select @val1 = query1.avg1
select @val2 = query2.avg2
select (@val1 / @val2)
But Justin's way is better.
Brian
|
|
Reply By:
|
mushu
|
Reply Date:
|
9/29/2004 2:48:16 AM
|
Thank you bmains and Justin, I am starting with SQL, and I am a little bit lost.
Mushu
|
|
Reply By:
|
bmains
|
Reply Date:
|
9/29/2004 9:50:01 AM
|
You might be interested in this: http://www.sqlservercentral.com/articles/articlesexternal.asp?articleid=1522.
Brian
|
