They flog dead horses don't they?

Pholks, Some time ago we had a loverly discussion of the qualifications of those of us w/ and w/o CS degrees. While I have no desire to resurrect that discussion an example has come to light which portrays some of the differences. Dick Hassler asked for a way to solve his scoring problem - essentially to return an average score from 5 scores, tossing the highest and lowest scores. A table of that would look something like: #scores do: 5 toss high and low - return avg. 4 toss high - return avg. 3-0 return avg. The scores where all stored in one row. Hence making Max()/Min()/Sum() as such impossible. Following my claims that I solved all problems that I couldn't solve by asking my brother - Dick asked me to apply myself to this problem. I duly came up with a solution and then passed the request to my brother for his $.02. Here, thought I, is a prime case - My brother has a Phd in Topology (volumes of surfaces in rotation - i.e. the hole - not the doughnut). His career has focused more on the R&D side of things. I got bored with math early on and opted for a BA in History followed by a CLC programming certificate. My career has been DP oriented. My solution: 3 - instead create a new column in the table with an average score in it. IMHO it's unique enough data that it's not 'easily' computed from the other values. Then create a process which updates this field based on the contents of the other fields. If you want to automate that you might consider a trigger/spl combo (provided your engine is online 5.01 or higher). 4 - the process would look something like: select score1, score2, score3, score4, score5... ow! they're in one row - now I see what you mean about max/min ok - let's switch tracks. You can do it with a bunch of if statements - you can figure out that one, or switch and do it in sql/spl. solution 1: create procedure col_avg(sc1 dec(4,2), sc2 dec(4,2), sc3 dec(4,2), sc4 dec(4,2), sc5 dec(4,2)) returning decimal(8,4); define mx decimal(4,2); define mn decimal(4,2); define ctr smallint; define avgscr decimal(8,4); { get the data into an sql workable form } create temp table junk (scr dec (4,2)); insert into junk values (sc1); insert into junk values (sc2); insert into junk values (sc3); insert into junk values (sc4); insert into junk values (sc5); { get max and min } select max(scr) into mx from junk; select min(scr) into mn from junk; { min will work fine since a 0 will be the min, max is another story } select count(*) into ctr from junk where scr>0; if ctr < 4 then let mx=0; else let ctr = 3; end if; select sum(scr) into avgscr from junk; let avgscr = avgscr - mx; let avgscr = avgscr - mn; let avgscr = avgscr / ctr; drop table junk; return avgscr; --------------------------------------------- My brothers answer I assume that you can get the number of entries that have a value. That is, if a[5] = {0, 3, 2, 0, 0} count(a) = 3 - the number of slots that hold a 0. We need the sum of three integers. We then divide by 3. if (count(# == 0) > 1) sum = sum of all of the numbers else sum = sum of all of the numbers - min - max Another way to do this is sum = sum of all - min - max if (count(# == 0) > 1) sum = sum + max --------------------------------------------- (He has no experience with SQL so I had to code the above into...) This would look similar except that the select statement would be cleaner: SELECT AVG(SUM(scr)) FROM junk WHERE scr > 0 HAVING COUNT(*) <= 3 UNION SELECT AVG(SUM(scr) - MIN(scr) - MAX(scr)) FROM junk WHERE scr > 0 HAVING COUNT(*) > 3 It would thus have to do one read vs my simplistic 4. --------------------------------------------- On the other hand, he didn't address how to get the data into a form in which it could be manipulated in this fashion. --------------------------------------------- cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ If anything can go wrong, fix it. To hell with Murphy. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________