Arno Brinkman wrote:

> Hi,
>
>>> select
>>> Count(distinct id_apos),
>>> Count(distinct f_id_auftrag),
>>> Count(distinct f_id_kunde),
>>> Min(f_weight),
>>> Sum(f_quantity)
>>> FROM
>>> t_apos ...
>>> JOIN...
>>> WHERE ...
>>>
>>
>> this only gives me the lowest weight, not the sum of all weights
>> (counted only one time for every id_apos).
>
> I still not completly understand what you want, but may be you can
> achieve this by splitting it into two queries.
>
> Create a VIEW (fe VIEW1) with
>
> SELECT
> id_apos,
> Count(distinct f_id_auftrag) AS f_id_auftrag,
> Count(distinct f_id_kunde) AS f_id_kunde,
> Min(f_weight) AS f_weight,
> Sum(f_quantity) AS f_quantity
> FROM
> t_apos ...
> JOIN...
> WHERE
> ...
> GROUP BY
> id_apos
>
> and use the VIEW in a select
>
> SELECT
> Count(*),
> Sum(f_id_auftrag),
> Sum(f_id_kunde),
> Sum(f_weight),
> Sum(f_quantity)
> FROM
> VIEW1
>
>
> The problem is that the WHERE clause is going into the VIEW and when
> that is dynamic it is not possible. In that case then you've to write
> a Stored Procedure where you can do it all as you want :)
>

I want to count all orders and customers and want the sum of all weights and
quantities.
But weights and quantities should only be counted once per order.
I thought this is a standard problem.
I cannot use a view because joins and where clause is dynmacily created.
I would need a select on a select(temporary tables), but this seems not to
be possible in Firebird.

mfg
Carsten