title: How to avoid double counting in SQL query
body: <p>I have two tables.</p>
  
  <p>Table <strong>X</strong> with columns as Productid, Subproductid and Price.</p>
  
  <p>Table <strong>Y</strong> with columns Productid, Subproductid and Weight.</p>
  
  <p>Data is as follows:</p>
  
  <p><strong>Table X:</strong>                                                          </p>
  
  <pre><code>+-----------+--------------+-------+
  | Productid | Subproductid | Price |
  +-----------+--------------+-------+
  | ProductA  | s1           |     4 |
  | ProductA  | s1           |     6 |
  | ProductA  | s2           |    12 |
  | ProductA  | s2           |     8 |
  | ProductB  | s1           |     3 |
  | ProductB  | s1           |     2 |
  | ProductB  | s2           |     6 |
  | ProductA  | s2           |     5 |
  +-----------+--------------+-------+
  </code></pre>
  
  <p><strong>Table Y:</strong>                                                          </p>
  
  <pre><code>+-----------+--------------+--------+
  | Productid | Subproductid | Weight |
  +-----------+--------------+--------+
  | ProductA  | s1           |      3 |
  | ProductA  | s2           |      5 |
  | ProductB  | s1           |      4 |
  | ProductB  | s2           |      1 |
  +-----------+--------------+--------+
  </code></pre>
  
  <p>For each product, I use Weighted price SQL as:</p>
  
  <pre><code>Select X.Productid, sum(X.price * Y.weight)/sum(Y.weight)
      from X,Y
      where X.Productid = Y.Productid
      And X.SubProductid = Y.SubProductid
  Group by X.productid
  </code></pre>
  
  <p>But by using above query, I am double counting Table Y instances.</p>
  
  <p>I want output as follows</p>
  
  <p><strong>For ProductA</strong>:
  Weighted price is (4*3 + 6*3  + 12*5 + 8*5)/(3+5) = 16.5</p>
  
  <p><strong>For ProductB</strong>:
  Weighted price is (3*4 + 2*4 + 6*1 +  5*1)/(4+1) = 6.2</p>
  
  <p>How do I write query to get above result without double counting Table Y rows?</p>