Aggregation, joining, and filtering with many-to-many table

Question

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.

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        |
+---------+------------+-------+----------+

My Materials table originally looked like this

+----------+-------------------------+
| Material |      Applicability      |
+----------+-------------------------+
| x        | car, house, plane, bike |
| y        | car, bike               |
+----------+-------------------------+

When I need to display what materials John buys and which objects that material is applicable to, my query is this.

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

The result

+----+------+--------------+----------+-------------------------+
| ID | Name | Total Sales | Material |      Applicability      |
+----+------+--------------+----------+-------------------------+
|  1 | John |           25 | x        | car, house, plane, bike |
|  1 | John |            6 | y        | car, bike               |
+----+------+--------------+----------+-------------------------+

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.

Now it's been decided to normalize the Materials table. The table relationship is many to many. A material can be applicable to many objects, and objects can contain many materials.

+----------+---------------+
| Material | Applicability |
+----------+---------------+
| x        | car           |
| x        | house         |
| x        | plane         |
| x        | bike          |
| y        | car           |
| y        | bike          |
+----------+---------------+

This normalization has disrupted my existing query, it causes the sum(sales) result to be a multiple of however many objects the material is applicable to.

Example.

+----+------+-------------+----------+---------------+
| 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          |
+----+------+-------------+----------+---------------+

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.

The main problem is when i need to find out what John buys, but also filter by applicability.

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

If any material is applicable to both car and bike, then the aggregate value sum(sales) will be doubled.

How do i deal with this duplication? I'm open to getting the result set in a different format, I just want the aggregation results to be correct.

Answer 1

if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).

I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?

select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as 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 OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material

Answer 2

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'
GROUP BY ID, Name, Material, Applicability 
UNION
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 = 'bike' 
GROUP BY ID, Name, Material, Applicability