The goal is to get an average, minimum, and maximum of all non-zero values
for columns btu_inj and btu_with..In MS Excel, you can do this using an
array formula to exclude zero values.
EXAMPLE: Values 0,1,2,3,4,5 should give MIN=1, MAX=5, AVG=3 (not 2.5)..
I appreciate everyone's input and solutions; it is amazing to me that all of
the solutions look like they would do the trick, but produce completely
different results than you would expect!
Here are the results of what I tried:
***** Solution using HAVING:
"select #1=10 (avg(btu_inj)) (min(btu_inj))=10 (max(btu_inj))=10
(avg(btu_with))=10 (min(btu_with))=10 (max(btu_with))=10 from + minmax5
group by #1 + HAVING (avg(btu_inf)) <> 0 + OR (min(btu_inf)) <> 0 + OR
(max(btu_inj)) <> 0 + OR (min(btu_with)) <> 0 + OR (max(btu_with)) <> 0"
This one looked promising - but I think what it actually doing is weeding
out rows that have an avg, min, or max BTU of zero instead of ignoring 0
values from the view .. I am not positive of this but the data output result
shows that the average numbers for each yr/month are obviously using the
zeroes. Example, where one month had 29 values of 0 and one value of 1040,
the average was given as 34 instead of 1040..
***** The solution using two selects, using the same view:
"select #1=10, (avg(btu_inj)), (min(btu_inj))=10, (max(btu_inj))=10
from minmax5 group by #1 where btu_inj <> 0" AND
"select #1=10, (avg(btu_with))=10, (min(btu_with))=10, +
(max(btu_with))=10 from minmax5 group by #1 where btu_with <> 0"
This looked like it would work also - and it did, but it *completely*
excluded months that had 0s in the month as opposed to just excluding the 0s
from the calculations.. It only displayed results for months that didn't
have zeroes in that month for the respective column!
***** This solution:
"select #1=10 (avg(btu_inj)) (min(btu_inj))=10 (max(btu_inj))=10
(avg(btu_with))=10 (min(btu_with))=10 (max(btu_with))=10 from + minmax5
where btu_inj<>0 group by #1"
Excludes months where any row in the view has a zero. And adding the
condition "AND btu_with<>0" excludes even more..
***** What I ended up with:
It appears that the quickest solution, although not as elegant as I had
intended, is to create two views, one for btu_inj and one for btu_with, and
exclude the zero values in the view. THEN use the select to get the average,
minimum, and maximum..This isn't optimal because I wanted it all in one
table-like display - I guess I can't have everything!
create view minmax6 (yr_month, btu_inj) +
as select ( (ctxt( (iyr(gasday)))) + '/' + (ctxt( (imon(gasday)))) )
btu_inj from daily_data where btu_inj <> 0 group by #1 btu_inj
create view minmax7 (yr_month, btu_with) +
as select ( (ctxt( (iyr(gasday)))) + '/' + (ctxt( (imon(gasday)))) )
btu_with from daily_data where btu_with <> 0 group by #1 btu_with
select #1=10, (avg(btu_inj)), (min(btu_inj))=10, (max(btu_inj))=10 from
minmax6 group by #1
select #1=10, (avg(btu_with)), (min(btu_with))=10, (max(btu_with))=10 from
minmax7 group by #1
Charley
Charles Sikora
Coordinator, Gas Storage
Manlove Field
Peoples Energy Corporation
(217)-897-7123
-----Original Message-----
From: J. Stephen Wills [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 18, 2003 3:54 PM
To: [EMAIL PROTECTED]
Subject: [RBASE-L] - Re: RBW6.5++: How to ignore zero?
Couldn't you just add (to) a WHERE :
Either in your VIEW-statement :
WHERE ...
btu_inj > (0)
but_with > (0)
Or to your SELECT-statment :
WHERE ...
btu_inj > (0)
but_with > (0)
Depending on whether you want the 0-values excluded at the point of the VIEW
or at the point of the SELECT result-set?
Am I missing something or could it be so done? If not, I think there are
other approaches, but I'd to hear more about your requirements and maybe a
small block of relevant test data ...
Thanks,
Steve in Memphis
