Well, you have to decide how to treat this in the sum. The proposed
solution adds all items together, if you only want one of the tests to
be included, you have to eliminate the other(s) in your WHERE clause.
More complex calculations (like sum the average score in case of double
entries for some registrations) may require using other formulae than
simply SUM.

Set

jft wrote:

> Does the requirement:
> "A tester can apply the same test to the same patient the same day more than once"
> complicate the proposed solution?
> John
>> -------Original Message-------
>> From: Alejandro Garcia <aleplgr@...>
>> Subject: Re: [firebird-support] Sum of rows
>> Sent: 16 Apr '07 19:47
>>
>> Yes, thanks, you are right, this one works perfect :
>>
>> select PatientID, Date, Tester, Sum(Points) as SumPoints
>> from table
>> where Items in ('itemA', 'itemB', 'itemC')
>> group by PatientID, Date, Tester
>>
>> Svein Erling Tysvaer <[LINK:
>> mailto:svein.erling.tysvaer%40kreftregisteret.no]
>> svein.erling.tysvaer@...> escribió:
>> Why not simply something like:
>>
>> insert into summaryTable(PatientID, Date, Tester, SumPoints)
>> select PatientID, Date, Tester, sum(Points)
>> from MyTable
>> group by 1, 2, 3
>>
>> HTH,
>> Set
>>
>> Alejandro Garcia wrote:
>> > Hi! I have a table of patients who executed a test, each test has 10
>> items =
>> > and I have to generate a new column with the sum of 3 of those 10
>> items.
>> > The original table columns are this: PatientID Date Items Points
>> Tester.
>> > Date is the date the test took place, a test is started and finished at
>> the=
>> > same date
>> > Items are the 10 items of the test, one in each row of the table
>> > Tester is the doctor that applied the test to the patient.
>> > Points is the score that the patient got in that item, that date,
>> applied b=
>> > y that tester.
>> > A tester can apply the same test to the same patient the same day more
>> than=
>> > once.
>> > Two different testers can apply the same test to the same patient the
>> same =
>> > date so that date one patient will have 2 scores for each item.
>> >
>> > I need to create a table with this columns:
>> > PatientID Date Tester SumPoints
>> > (where SumPoints is the sum of 3 known items of the test, for example
>> Item1=
>> > , Item2 and Item3)=20
>> >
>> > I thought of doing something like this:
>> > find maxpat minpat
>> > while minpat < maxpat
>> > select minpat date
>> > select minpat tester
>> > select minpat item1
>> > select minpat item2
>> > select minpat item3
>> > result=3D item1+item2+item3
>> > add result to final table
>> > find another tester for the same date and patient calculate result, add
>> to =
>> > final table
>> > find another date for the same tester and patient calculate result add
>> to f=
>> > inal table
>> > increment minpat
>> >
>> > what do you think? could this work? is there a better way to do it?
>> > Thanks in advance