Help with union query 1

[mptwoadmin]

I have the follwoing query can someone help explain how I can get the results i need?

I get an "Invalid column name 'max_value2'" error.

Thanks


SELECT tagname, sum(max_value1-max_value2) FROM
(
select tagname, max(value) as max_value1
from analoghistory
where tagname = 'wf_lb26_dat_cray_produced'
and datetime = '2009-09-29 06:46:22.0'
group by tagname
union all
(select tagname, max(value) as max_value2
from analoghistory
where tagname = 'wf_lb26_dat_cray_produced'
and datetime <= {ts '2009-09-29 07:45:00'}
group by tagname)) c
group by max_value1, tagname;

 

[PWise]

try
SELECT tagname, sum(max_value) FROM
(
select tagname, max(value) as max_value
from analoghistory
where tagname = 'wf_lb26_dat_cray_produced'
and datetime = '2009-09-29 06:46:22.0'
group by tagname
union all
(select tagname, -max(value) as max_value
from analoghistory
where tagname = 'wf_lb26_dat_cray_produced'
and datetime <= {ts '2009-09-29 07:45:00'}
group by tagname)) c
group by max_value1, tagname

 

[PWise]

sorry SB

SELECT tagname, sum(max_value) FROM
(
select tagname, max(value) as max_value
from analoghistory
where  tagname = 'wf_lb26_dat_cray_produced'
and datetime = '2009-09-29 06:46:22.0'
group by tagname
union all
(select tagname, -max(value) as max_value
from analoghistory
where  tagname = 'wf_lb26_dat_cray_produced'
and datetime <= {ts '2009-09-29 07:45:00'} 
group by tagname)) c
group by tagname

 

[mptwoadmin]

thanks pwise, i'll try it out when i get to work.

 

[mptwoadmin]

PWISE - I tried your query works well but I get a negative number anyway of doing away with the "-"?

Thanks

 

[PWise]

how about

SELECT tagname, Abs(sum(max_value))
FROM.........