--- In firebird-support@yahoogroups.com, Carsten Schäfer wrote:

> Hi,
> I ' ve a sql problem and want to resolve it with Firebird 1.5.1.
> Follwing select:
> select count(distinct id_apos),count(distinct f_id_auftrag),count(distinct
> f_id_kunde),sum(f_weight),sum(f_quantity) FROM t_apos ...JOIN...
> WHERE ...
>
> My problem is that i have some doubles in id_apos in the select and i want to
> sum the weight of every apos only one time. Is this possible with normal sql?

Hi Carsten!

Of course it is possible to do something like

select sum(f_weight)
from t_apos a1
where not exists(select * from t_apos a2
where a2.id_apos = a1.id_apos
and a2.pk < a1.pk)

if you want to sum things only once. Whether this can be included in your
bigger query where you occationally want to count several instances is doubtful.
I'm not certain whether something like

sum(case a1.pk >
(select min(a2.pk) from id_apos a2
where a2.id_apos = a1.id_apos) then 0
else f_weight) end

will work or not - the examples in the release notes are nowhere near as complex
as this (case containing a subselect as part of a sum function, I'd be impressed
if this is part of Fb 1.5), and I cannot test since I haven't got Fb 1.5
available from here.

HTH,
Set