I would do this way:

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

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

HTH
Marcin



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
>
>
>
>