title: How to find Profit for Category
body: <p>My project is about a jewelery store and i try to find the profit of each product-category.
  Let me be more specific</p>
  
  <p>I have 3 tables which gives me the info: </p>
  
  <p>SALES(salesid,productid,quantity,price)</p>
  
  <pre><code>salesid  productid   Quantity Price
  11001    13001       4        5
  11002    13002       6        10
  11003    13003       5        16
  .
  .
  11012    13012       7        15
  </code></pre>
  
  <p>RETURN(salesid,productid,date,quantity,price)</p>
  
  <pre><code>salesid  productid   Quantity Price
  11003    13003       1        16
  11007    13007       3        12
  11008    13008       3        8
  </code></pre>
  
  <p>PROCUREMENT(procurementid,productid,quantity,price)</p>
  
  <pre><code>procurementid  productid   Quantity Price
  100001         13001       10       2
  100002         13002       10       2
  .
  . 
  100012         13012       10       2
  </code></pre>
  
  <p>product_category(categoryid,category)</p>
  
  <pre><code>categoryid  category
  1           Gold
  2           Silver
  .
  5           Platin
  </code></pre>
  
  <p>product(Productid,categoryid)</p>
  
  <pre><code>Productid  categoryid
  13001      1
  13002      3
  .
  .
  13010      5
  </code></pre>
  
  <p>The profit is given from this type: </p>
  
  <pre><code>Profit=Quantity*Price(Sell)-Quantity*Price(Return)-Quantity*Price(Procurement)
  </code></pre>
  
  <p>And now here is the problem.. I came up to this so far</p>
  
  <pre><code>SELECT categoryid,
         category,
         (coalesce(a.rev,0)- coalesce(b.ret,0),
                             coalesce(c.cost,0)) AS profit
  FROM product category AS g
      JOIN product AS h ON g.categoryid = h.categoryid
      JOIN
        (SELECT categoryid,
                sum(quantity*price) AS rev
         FROM sales AS a,
              product AS b
         WHERE a.productid = b.productid
         GROUP BY categoryid) a
      LEFT OUTER JOIN
        (SELECT cartegoryid,
                sum(quantity*price) AS ret
         FROM RETURN AS a ,
                        product AS b
         WHERE a.productid = b.productid
         GROUP BY categoryid) b ON a.categoryid = b.categoryid
      LEFT OUTER JOIN
        (SELECT categoryid,
                sum(quantity*price) AS cost
         FROM procurement AS a,
              product AS b
         WHERE a.productid = b.productid
         GROUP BY categoryid) c ON a.categoryid = c.categoryid ,
      product AS d,
      procurement AS e
  WHERE MONTH(f.date) = MONTH(e.date)
    AND YEAR(date) = 2013 
  </code></pre>
  
  <p>[sorry for the align i am new to the site dont know how to copy paste code well(:D)]
  wahtever when i do this it comes to a state like</p>
  
  <pre><code>categoryid  category  profit
  1           Gold      -100
  2           Silver    -100
  .
  5           Platin    -100
  </code></pre>
  
  <p>dont know where is the problem...i made a lot of changes and switches but nothing came up...any suggestion would be so helpfull.Thank u in advane</p>