This will do exactly what you want without using a cursor. I created a
test database TEST with only one table to demonstrate this command set,
and make sure I had no typos. This set of commands will create the test
database, table and insert one row, then calculate your count for you as
a demo. I did not bother with the other columns.
CREATE SCHEMA AUTHOR Test PUBLIC
CREATE TABLE `Test` +
(`OHcnt1` INTEGER , +
`OHCnt2` INTEGER , +
`OHCnt3` INTEGER , +
`OHCnt4` INTEGER , +
`OHcnt5` INTEGER , +
`CountDenom` INTEGER )
LOAD `Test`
NONUM
8,0,15,2,0,NULL
END
UPDATE test SET CountDenom = 0
UPDATE test SET CountDenom = (IFGT(OHcnt1,0,1,0))
UPDATE test SET CountDenom = (CountDenom + (IFGT(OHcnt2,0,1,0)))
UPDATE test SET CountDenom = (CountDenom + (IFGT(OHcnt3,0,1,0)))
UPDATE test SET CountDenom = (CountDenom + (IFGT(OHcnt4,0,1,0)))
UPDATE test SET CountDenom = (CountDenom + (IFGT(OHcnt5,0,1,0)))
RETURN
On 02/03/2011 10:49 AM, Brad Davidson wrote:
Yes, that's true, but I want to define a column which counts the OHcnt
columns> 0 and not sure how to do this at the table level without
"cursoring" through the table.
Thanks,
Brad
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry
Sent: Wednesday, March 02, 2011 8:37 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Averaging
For your count denominator try
COUNT(*) ... WHERE value> 0
You should be able to do this in a computed column.
Albert
On 02/03/2011 8:34 AM, Brad Davidson wrote:
Pardon the wrapping, perhaps this will read better:
Count Years 1 thru 5: 8 0 15
2 0
Avg Years 1 thru 5: 100 0 75
125 0
I want to add 2 columns to my temporary table (for reporting) showing
the total number of serviced items (total count, no problem), and, the
average service dollars (my challenge).
Therefore, the number of AVG columns> 0, in this case, 3 as a divisor
with the sum of averages as the dividend (100+75+125=400). So, my
quotient would be 400 divided by 3 = 133 and change.
As all the averaging for these rows of numbers is part of my nightly
maintenance (re-populating a table with a revolving 5 year history of
data), I really don't want to sum service dollars again in performing
this task, though this method would yield the results I want. Just
that the performance would really be slow. Rather, I'd like to do the
above, but, am failing to come up with the math or function to do so
in creating my "Average" columns for years 1 thru 5. Any ideas
appreciated!
Brad Davidson
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME,
DIGEST, RESEND, HELP) ================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected] In the message SUBJECT,
put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected] In the message SUBJECT,
put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected] In the message SUBJECT,
put just one word: SEARCH-n (where n is the number of days). In the
message body, place any text to search for.
================================================
