Svein Erling Tysvær wrote:

> --- In firebird-support@yahoogroups.com, Carsten Schäfer wrote:
>> 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.
>
> If case is working approximately as I wrote (I'm pleasantly surprised)
> , then it should be possible to do something like
>
> sum(case ot.pk >
> (select min(ot.pk) from id_apos a2
> join OtherTable ot on ot.field = a2.field
> where a2.id_apos = a1.id_apos) then 0
> else f_weight) end
>

This is not working, too.
First ot.pk can not be referenced outside the select.
Second problem is that the other table (that is joined) has a compound
primary key (f_id_apos (fk from t_apos) and f_id_charge(fk from table
t_charge)

Carsten