Hello all. I once again have a SQL statement problem.

What Im wanting to do is SUM up the Quantity and price.

The SQL statement below does this just fine.


SELECT ScripSaleCart.CartID, ScripSaleProductMaster.Description, ScripSaleCart.Quantity, ScripSaleProductMaster.Price, SUM(Quantity)*(Price) AS Total
FROM ScripSaleProductMaster INNER JOIN ScripSaleCart ON ScripSaleProductMaster.ProductID = ScripSaleCart.ProductID
GROUP BY CartID, Description, Quantity, Price

Below are the results


CartID Description Quantity Price Total

1 test 6 25.00 150.00
2 test2 2 25.00 50.00

Now what i want to do is total up the Totals so that i can display the subtotal of the order to the customer.

If this possible to do by changing the above SQL statement?

Thanks Hooks

Firstly I would say you do not need the sum(quantity)*(price) and then of course the group by clause in the query that you have posted in order to get the result you have shown.

You could do that purely by putting quantity * price AS Total and then do away with the Group By. I would guess this would be quicker (albeit fractions fo a second).

To get the total for each cart you will need each item in the card linked to a consistant item (Cart No), (order no) or (customer no) then put something like:

select CartNo, sum(quantity*price) from cart
group by CartNo

This will give you the total sum for each cart in the table.

HTH

Thanks a bunch.