Hi George,

Thank you for answering me.

Let me try to be more specific, and hang on there, this might be an interesting problem:

First of all, there is no need of a separate HAWB table, but this is not relevant.

Hawb= {Shipments}

The shipment table might consist of the following fields:

Shipment= Shipment#+Value+Hawb,

the value being, well just that, the value of the shipment that is sent, i.e. the total of the invoice of what the exporter sends to the consignee.

The charges table might consit of the following fields:

Charges= Shipment#+{Service+ChargeAmount}

Now what I want is a view of the HAWB where

Hawb.Value for HAWB# = sum Shipment.Value where Shipment.Hawb= Hawb#

and

Hawb.ChargeAmount= sum Charges Amount where Charges.Shipment#=Shipment.Shipment# and Shipment.Hawb= Hawb#

Note semantically that the total value of the Hawb is relevant because it might be the basis for the insurance charge, and the importance total charges on HAWB level should be trivial: it is the turn over.

I hope that I convinced you that your suggested second join with the HAWB table is redundant, so the obvious sql would be:

select Hawb, sum(Value), sum(Charge);
from shipment,charges;
where shipment.shipment#= charges.shipment#;
order by hawb;
group by hawb.

If you take out the aggregation function (sums, order and group) and add the shipment# you will get a denormalised view hawb+shipment#+value+charge, the problem being that each shipment and value would be represented once per charge for that shipment, which is OK if you totalize the charges, but a problem if you totalize the value.

Example:

HAWB 1 consists of Shipment 1 of a value of 10000 usd and Shipment 2 of a value of 20000 usd. That total value of the HAWB is 30000 usd OK? Now assume that there are two charges for shipment 1 and 1 charge for shipment 2, then the value following the sql would be 40000, see?

I guess there _is_ no way to do this with one SQL, because you can only group on one level per SQL.

Wonder how one solves this in a C/S environment...

Marc