This is certainly one way to go on this - thank you, Albert for your time on this, much appreciated!
Brad -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry Sent: Wednesday, March 02, 2011 10:35 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Averaging 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. >> ================================================ >> >> >> > >
