Calculation with two averages

mushu · September 22nd, 2004, 11:18 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

bmains · September 22nd, 2004, 11:34 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

Justin Cave · September 22nd, 2004, 11:56 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

Code:

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

mushu · September 23rd, 2004, 12:01 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

mushu · September 28th, 2004, 06:48 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

bmains · September 28th, 2004, 06:56 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

mushu · September 29th, 2004, 02:48 AM

Thank you bmains and Justin, I am starting with SQL, and I am a little bit lost.

Mushu

bmains · September 29th, 2004, 09:50 AM

You might be interested in this: http://www.sqlservercentral.com/arti...articleid=1522.

Brian