Posted by Stefan Rybacki on 08/25/05 01:23

Joel wrote:
> Hi,
>
> I'm writing an Sql Statement to total the sale of an order
> TotalSale=(TotalSale + (QtyShipped * Price)). I have the syntax & it works
> great.
> However, I'd like to add Tax + Freight that is derived from another table.
> How do I get it to add only once.
>
> Here's the Statement:
>
> "Select Orders.`Order_#`, Orders.`Cust_#`, Orders.`Name`, Orders.Date,
> Orders.Status, Orders.`Po_#`, Sum(Olitems.Qty_Shipped * Olitems.Price) As
> Total_Sale From Orders, Olitems Where Orders.`Order_#` = Olitems.`Order_#`
> Group By Orders.`Order_#` Order By Orders.`Order_#` "
>
> Olitems=Line Item Table
> Orders=Header Table
>
> I'd like to include:
> Sum((Olitems.Qty_Shipped * Olitems.Price) + Orders.Tax + Orders.Freight) As
> Total_Sale
>
> When I do that it keeps on adding tax & freight for each line item! How to
> work around that?
>

- Do 2 queries. One which lists each item (the above one) and one that just lists the sum
(use GROUP BY to sum all items)
- Do your query and calculate sum, taxes and whatever in the programming language of your
choice

Stefan


> Thanks for all your help.
>
>
>
>

[Back to original message]