Thank you... works for me as well with 2.1.3 and 1.5.4, same tables,
same misbehaving records in them... now I just got to find a way to
remember this for the future, because somehow the first command looked
more logical to me :)


MB> I would do this way:

MB> SELECT
MB> T.ID,
MB> SUM((CASE WHEN (T.UNITS > 0) THEN T.DIFF/T.UNITS
MB> ELSE T.DIFF
MB> END))
MB> FROM AGGUNITS T
MB> WHERE T.DIFF > 0.05
MB> GROUP BY 1

MB> This 'construction' worked for me many times...

MB> HTH
MB> Marcin



MB> W dniu 16.02.2010 14:45, André Knappstein, Controlling pisze:

>> Hello,
>>
>> unfortunately I don't have Helen's book here with me and
>> *googling*/*binging* the question has found the following plus other
>> sites, but could not answer my question:
>> http://www.janus-software.com/fbmanual/manual.php?book=psql&topic=56
>>
>> In the process of planning a migration 1.5.x -> 2.1.3 I am reworking
>> some commands to clean some tables for which I did not have a chance
>> to validate them client sided and omitted to do so on server side.
>> I wanted to except a rare case where a division by zero could occur.
>>
>> But the following does throw a server exception "Invalid expression in
>> the select list (not contained in either an aggregate function or the
>> GROUP BY clause). How would I put that syntax right?
>>
>> Select
>> T.ID,
>> CASE
>> WHEN (T.Units> 0) THEN sum(T.Diff/T.Units)
>> ELSE sum(T.Diff)
>> END
>> from
>> AggUnits T
>> where
>> T.Diff> 0.05
>> group by
>> 1
>>
>> Is that possible in 1.5.x or only in 2.1.x or does aggregating within
>> conditional clauses not work at all... I think I knew, but I don't
>> remember... :)
>>
>> I also tried "GROUP BY T.ID" without success.
>>
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH
>> Hafenweg 4
>> 59192 Bergkamen-Rünthe
>>
>> Telefon: +49 2389 9240 0
>> Telefax: +49 2389 9240 150
>> e-mail: info@...
>>
>> Amtsgericht Hamm Nr. B 420 || USt-IDNr.: DE 125215402
>> Geschäftsführer: Achim Krähling, Dirk Salewski, Matthias Steinhaus
>>
>>
>> ------------------------------------
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Visit http://www.firebirdsql.org and click the Resources item
>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>
>> Also search the knowledgebases at http://www.ibphoenix.com
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> Yahoo! Groups Links
>>
>>
>>
>>

MB> ------------------------------------

MB> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

MB> Visit http://www.firebirdsql.org and click the Resources item
MB> on the main (top) menu. Try Knowledgebase and FAQ links !

MB> Also search the knowledgebases at http://www.ibphoenix.com

MB> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MB> Yahoo! Groups Links



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
beta Eigenheim- und Grundst�cksverwertungsgesellschaft mbH
Hafenweg 4
59192 Bergkamen-R�nthe

Telefon: +49 2389 9240 0
Telefax: +49 2389 9240 150
e-mail: info@...

Amtsgericht Hamm Nr. B 420 || USt-IDNr.: DE 125215402
Gesch�ftsf�hrer: Achim Kr�hling, Dirk Salewski, Matthias Steinhaus