title: Normalization , aggregation, and joining tables issue
body: <p>Let's say i have a database of customers who buy materials that are "applicable" to random objects. For example, John buys $10 of "Material X" that is applicable to a car and a house.</p>
  
  <pre><code>Customers
  +----+-------+
  | ID | Name  |
  +----+-------+
  |  1 | John  |
  |  2 | Larry |
  +----+-------+
  
  Orders
  +---------+------------+-------+----------+
  | OrderID | CustomerID | Sales | Material |
  +---------+------------+-------+----------+
  |       1 |          1 |    10 | x        |
  |       2 |          1 |    15 | x        |
  |       3 |          1 |     6 | y        |
  |       4 |          2 |     3 | x        |
  |       5 |          2 |    25 | y        |
  +---------+------------+-------+----------+
  </code></pre>
  
  <p>My <code>Materials</code> table originally looked like this</p>
  
  <pre><code>+----------+-------------------------+
  | Material |      Applicability      |
  +----------+-------------------------+
  | x        | car, house, plane, bike |
  | y        | car, bike               |
  +----------+-------------------------+
  </code></pre>
  
  <p>When I need to display what materials John buys and which objects that material is applicable to, my query is this.</p>
  
  <pre><code>Select ID, Name, sum(Sales), Material, Applicability
  FROM Customers a 
  INNER JOIN Orders b on a.ID = b.CustomerID
  INNER JOIN Materials c on b.Material = c.Material
  WHERE Name = 'John' 
  GROUP BY ID, Name, Material, Applicability
  </code></pre>
  
  <p>The result </p>
  
  <pre><code>+----+------+--------------+----------+-------------------------+
  | ID | Name | Total Sales | Material |      Applicability      |
  +----+------+--------------+----------+-------------------------+
  |  1 | John |           25 | x        | car, house, plane, bike |
  |  1 | John |            6 | y        | car, bike               |
  +----+------+--------------+----------+-------------------------+
  </code></pre>
  
  <p>The comma separated values (i know it violates many rules) was convenient, because when parsing the applicability i could simply split the string by commas and then i had a list of applicability objects.</p>
  
  <p>Now it's been decided to normalize the <code>Materials</code> table, so now it looks like this</p>
  
  <pre><code>+----------+---------------+
  | Material | Applicability |
  +----------+---------------+
  | x        | car           |
  | x        | house         |
  | x        | plane         |
  | x        | bike          |
  | y        | car           |
  | y        | bike          |
  +----------+---------------+
  </code></pre>
  
  <p>This normalization has disrupted my existing query, it causes the <code>sum(sales)</code> result to be a multiple of however many objects the material is applicable to.</p>
  
  <p>Example.</p>
  
  <pre><code>+----+------+-------------+----------+---------------+
  | ID | Name | Total Sales | Material | Applicability |
  +----+------+-------------+----------+---------------+
  |  1 | John |          25 | x        | car           |
  |  1 | John |          25 | x        | house         |
  |  1 | John |          25 | x        | plane         |
  |  1 | John |          25 | x        | bike          |
  |  1 | John |           6 | y        | car           |
  |  1 | John |           6 | y        | bike          |
  +----+------+-------------+----------+---------------+
  </code></pre>
  
  <p>Now it looks John has bought $100 of material x, when he has really only bought $25. I need to show the user John's purchase of material x, as well as x's applicability. </p>
  
  <p>The main problem is when i need to find out what John buys, but also filter by applicability.</p>
  
  <pre><code>Select ID, Name, sum(Sales), Material, Applicability
  FROM Customers a 
  INNER JOIN Orders b on a.ID = b.CustomerID
  INNER JOIN Materials c on b.Material = c.Material
  WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
  GROUP BY ID, Name, Material, Applicability
  </code></pre>
  
  <p>If any material is applicable to both car and bike, then the aggregate value <code>sum(sales)</code> will be doubled.</p>
  
  <p>How do i deal with this duplication?</p>