Dfyjkt

I am trying to build a query to create a basket analysis of products based on their type grouping. They have two levels of grouping beyond the product ids.

Dept level 
1 
2 
3
4

and buying group level

MA 
M
MC
WA
W
WC
KA
KC
K

the hierarchy is

• 1 > W, WC


• 2 > M, MC


• 3 > K, KC


• 4 > MA, KA, WA

right now the query I have

Select 
 i.buying_group,
 Sum(d.sales),
 Sum(d.units),
 count(distinct d.trans_nbr) transaction_count
From 
 sales_details d, item_data i, (select trans_nbr from sales_details where item_dept = 1 group by trans_nbr) main_group
Where 
 d.trans_nbr = main_group.trans_nbr
 d.item_nbr = i.item_nbr
 group by i.buying_group;

Right now I get the data that I need for most of the buying groups but because this is being run at the dept level it does not give me the correct basket information for W and WC. Is there a way to do this at the dept level that would show if the customer bought something from either of these groups and had the other in their basket without double counting it?

the results at the moment are something like this

buyyin_group Sum(sales) Sum(units) transaction_count
MA 100 5 4
M 75 3 3
MC 56 1 1
WA 48 3 2
W 250 6 6
WC 200 9 9
KA 164 7 5
KC 400 12 7
K 521 14 12 `