How to get the discount of items

I have 2 tables, one of them holds an initial discount for an item and the other holds a discount that a clerk may give in addition to the initial discount, also on that item. Any number of items can be sold and have discounts applied.

What I'm attempting to do here is 开发者_运维技巧group by order_id and get the total dollar amount for the discount. I don't have this correct because I am getting .19 cents when it should be roughly $5 something on a $10.00 purchase where each item was discounted 50%.

How do I calculate this?

SUM(price*quantity)*(SUM(a.discount)+b.discount) AS discount

For a single order item, you have a price, a quantity, an initial discount (there is always a discount for every order, though it could be zero) and the optional discount associated with a particular order item. So, the tables are ORDER and ORDER_IITEMS. Given that the discounts are stored as fractions (0.000 .. 1.000), you probably need a computation based on:

(price * quantity * (initial_discount + optional_discount))

to get the discount for a single item; you aggregate across that entire expression. So, you end up with something like:

SELECT Order_ID, SUM(price * quantity * (initial_discount + optional_discount))
  FROM Orders AS O
  JOIN Order_Items AS I ON O.Order_ID = I.Order_ID
 GROUP BY Order_ID;

Applying aliases to column names in the SUM:

SELECT Order_ID, SUM(I.price * I.quantity *
                     (O.initial_discount + I.optional_discount)) AS discount
  FROM Orders AS O
  JOIN Order_Items AS I ON O.Order_ID = I.Order_ID
 GROUP BY Order_ID;