title: SQL Basket Analysis grouping query
body: <p>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.</p>
  
  <pre><code>Dept level 
  1  
  2   
  3
  4
  </code></pre>
  
  <p>and buying group level</p>
  
  <pre><code>MA  
  M
  MC
  WA
  W
  WC
  KA
  KC
  K
  </code></pre>
  
  <p>the hierarchy is </p>
  
  <ul>
  <li>1 > W, WC</li>
  <li>2 > M, MC</li>
  <li>3 > K, KC</li>
  <li>4 > MA, KA, WA</li>
  </ul>
  
  <p>right now the query I have </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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? </p>
  
  <p>the results at the moment are something like this </p>
  
  <pre><code>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                  ` 
  </code></pre>