I am having trouble with my math on this query. Here is what I am trying to achieve: I have Item price and quantities which are multiplied and then shipping and taxes are added to that result. The problem is that when a Item number is a duplicate, I want add the multiply the item prices times their quantity and then add those together, then I need to add the tax and shipping only once to that result. I can see it but I can't wrap my mind around it. Here is the code and my current result set:
SELECT
o.OrderDate AS 'Order Date',
od.OrderID AS 'Order #',
od.ProductCode AS 'Item #',
od.ProductName AS 'Description',
od.Quantity,
od.ProductPrice AS 'Item Price',
o.TotalShippingCost AS 'Shipping//Handling',
o.SalesTax1 AS 'Sales Tax',
((CAST(od.ProductPrice AS Float) * CAST(od.Quantity AS Integer)) + CAST(o.TotalShippingCost AS Float) + CAST(SalesTax1 AS Float))AS 'Net Sale'
FROM Orders AS o
INNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID
WHERE PaymentDeclined IS NULL
Order by o.OrderDate DESC
order date order # item # quantity item price shipping//handling sales tax net sale
6/25/2012 21:26 32744 Account 2 0 0 0 0
6/25/2012 21:10 32743 r-p52-0001 1 24.99 13.75 0 38.74
6/25/2012 21:09 32742 096-050609 1 39.99 14.1 0 54.09
6/25/2012 21:06 32741 097-080029 1 5.99 7.46 0.96 14.41
6/25/2012 21:06 32741 097-080008 1 5.99 7.46 0.96 14.41
6/25/2012 21:05 32739 P53-0005 1 0 0 0 0
6/25/2012 21:05 32740 R-P21-1030 1 59.99 23.47 0 83.46
6/25/2012 21:05 32738 096-050565 1 9.99 7.46 0 17.45
6/25/2012 21:02 32736 096-050609 1 39.99 9.98 0 49.97
6/25/2012 21:01 32735 090-020047 1 1.99 6.95 0 8.94
6/25/2012 21:01 32735 090-130022 1 1.99 6.95 0 8.94
6/25/2012 21:00 32734 Account 1 0 0 0 0
6/25/2012 20:55 32733 096-050610 1 35.99 8.35 3.15 47.49
6/25/2012 20:53 32732 096-050610 1 35.99 10.22 0 46.21
6/25/2012 20:49 32731 096-050611 1 29.99 14 0 43.99
6/25/2012 20:48 32730 096-050609 1 39.99 8.03 3.3 51.32
6/25/2012 20:45 32729 R-P10-1400F 1 19.99 13.43 1.35 34.77
6/25/2012 20:40 32728 096-050607 1 89.99 18.66 0 108.65
6/25/2012 20:40 32728 078-110218 1 12.99 18.66 0 31.65
6/25/2012 20:40 32728 078-110143 1 2.99 18.66 0 21.65
Trouble with Math
Topic Locked
Posted - 06/26/2012 : 12:28:33
