2

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.

  • Do you need to return applicability as well as filter by it? By removing it, you can use a SELECT DISTINCT. – Haris Khan Aug 3 '16 at 19:34
0
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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.