>What is required is a table by HAWB with the total values of the shipments
>and the total of the charges.
>
>Can one do this in 1 SQL?
>
>select HAWB, sum(shipment.value), sum(charges.amount);
>from Shipment,Charges;
>where shipment.shipment= charges.shipment;
>order by Hawb;
>group by Hawb
>
>gives faulty shipment values. The value gets multiplied by the number of
>charges for each shipment.
>
>Is this a classic, or worse a faq? In native VFP, you can solve the
>problem with cascated SQLs, but what if you are in an C/S environment?
>Just curious.
It used to be a classic even before the advent of SQL in Fox - it was,
for one, near to impossible to sum an invoice and add the tax total if
values were in detail table, and the tax total was in the master, if
you wanted to print totals of more than one invoice.
Yesterday I was just reviewing some SQL builder app (named Esperant,
www.sagus.com I think) where it has an example of exactly equivalent
situation, and it simply forbids such two-level sum() combinations.
The solution would be to keep the value at the detail level, or forget
about the amounts in this Select, or have amounts already subtotalled in
the shipment table. And yes, the worst solution - 2 selects?