I have a table of characterization data from a test of a certain chip where
I work. THe table consists of 5 columns:
sample int
spec text
temp int
voltage float
measurement float
Each sample chip (1-120) is tested at three temps and two voltages. The
column of interest is measurement. The problem is that some of the samples
were tested multiple times for one temp/voltage combination so I have
entries such as:
sample spec temp voltage measurement
1 Tdsu 25 4.5 1.12e-9
1 Tdsu 25 4.5 1.12e-9
1 Tdsu 25 4.5 1.3e-9
My requirement is to delete any duplicate rows (such as #2 in this case)
and then take the average of #1 and #3, replace #1 with that average
measurement and delete #3. I simply cannot figure out how to first get rid
of *just* #2 and then somehow find the case of #1 and #3 existing and then
do the average followed by removing #3. Using DISTINCT with SELECT will
obviously spit out #1 and #3 but that doesn't get me anywhere. I am
somewhat of a SQL novice so please have pity on me in your possible
explanations. My ultimate goal is to do these modifications without having
to go in and manually find these situations and then manually make the
changes. I think it is possible with SQL but I can't figure out how.
Thanks very much in advance.
-Al Arduengo