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

HTH,
Set