Normalization , aggregation, and joining tables issue

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, so now it looks like this

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

Please tag your database accordingly. And what are your desired results, the same as the original? If so you want to use something like `group_concat` to recombine the rows into a single column... — sgeddes, Aug 02 '16 at 21:48
Added the sql-server tag. Yeah the result should be the same, the only difference is the normalized Materials table. — Eric Guan, Aug 02 '16 at 21:49
Sql Server doesn't support `group_concat`. There are several examples on SO with how to do this though. Does this help: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server — sgeddes, Aug 02 '16 at 21:51

score 0 · Answer 1 · answered Aug 02 '16 at 21:51

0

It may be easiest (least modification to the original query) to just sum first, then join on the applicability afterwards:

; with CTE as (
    Select ID, Name, sum(Sales) as TotalSales, material
    From Customers a
    inner join orders b
    on a.ID = b.CustomerID
    group by ID, Name, Material
    )

select b.*, c.Applicability from CTE b
inner join Materials c on b.Material = c.Material
where...--insert selection criteria here

answered Aug 02 '16 at 21:51

APH

4,109
1
25
36

Hi, thanks for the answer. This still returns duplicate rows if i do `where applicability = 'car' or applicability = 'house'`. If there is one material that is applicable to both, it will return two rows. I think a simulation of mySql's group_concat would work but im not sure. – Eric Guan Aug 03 '16 at 18:26

score 0 · Answer 2 · answered Aug 03 '16 at 20:28

SELECT  'x' as Material       , 'car'   as Applicability         
INTO #Materials 
UNION ALL
SELECT 'x'       , 'house'        
UNION ALL
SELECT 'x'       , 'plane'       
UNION ALL
SELECT 'x'       , 'bike'         
UNION ALL
SELECT'y'      , 'car'          
UNION ALL
SELECT 'y'       , 'bike'  





SELECT    1 as OrderID ,          1  as CustomerID,    10  as Sales, 'x'  as    Material   
INTO #Orders
UNION ALL
SELECT    2 ,          1 ,    15 , 'x'        
UNION ALL
SELECT    3 ,          1 ,     6 , 'y'        
UNION ALL
SELECT    4 ,          2 ,     3 , 'x'        
UNION ALL
SELECT    5 ,          2 ,    25 , 'y'        



SELECT   1 as ID ,  'John'  as Name
INTO #Customers
UNION ALL
SELECT 2 , 'Larry';
with CTE as (
SELECT ID, Name, sum(Sales) as TotalSales, c.material, Applicability
From #Customers a
inner join #orders b
on a.ID = b.CustomerID
inner join #Materials as c on c.Material = b.Material
where Name = 'John' and (applicability = 'car' or applicability = 'house')
group by ID, Name, c.Material, Applicability
)


SELECT ID, Name, TotalSales, Material, STUFF(
(SELECT distinct ',' +   Applicability 
FROM cte as c 
where c.Material = c1.Material

FOR XML PATH (''))
, 1, 1, '') as Applicability
from CTE c1
group by ID, Name, TotalSales, Material

drop table #Customers
drop table #Orders
drop table #Materials

Hopefully, this is what you looking for.

Normalization , aggregation, and joining tables issue

2 Answers2