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
>
> ---------------------------------
>
> LLama Gratis a cualquier PC del Mundo.
> Llamadas a fijos y móviles desde 1 céntimo por minuto.
> [LINK: http://es.voice.yahoo.com] http://es.voice.yahoo.com
>
> [Non-text portions of this message have been removed]
>
>