Svein Erling Tysvær wrote:

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

case is working (with slight another syntax),
but problem is that id_apos is the primary key of t_apos so this will not
work,i think.

mfg
Carsten