SQL Server Performance

grouping and counting iteration

Discussion in 'SQL Server 2005 General Developer Questions' started by boutwater, Jun 23, 2011.

  1. boutwater Member

    Hello, I have a table that has an ID, customer, Date, OrderNum, and partSKU. I would like to report on how many times sku "x" is in the same order as sku "y" for each sku in the table. I'm not sure how to do this. An order could just be one line (so the above request would not be evaluated), or an order could be multiple lines. Please let me know if you need more info to answer. Thanks,

    Ben
  2. boutwater Member

    I'm trying to break this down into two parts. The first would be to make an intermediate table that has all the unique pairs. I guess an example may be better description. I one order in this table is:
    ID Order SKU
    1 104350 61226
    2 104350 61223
    3 104350 66728
    4 104350 66273
    5 104350 66273

    I would want the result to not have any duplicate record pairs. If I join on itself, I get 25 results and 15 of those are duplicates:
    select a.order, a.sku as sku1, b.sku as sku2
    from [parts_orders clean] a inner join [parts_orders clean] b on a.order = b.order
    where a.order = '104350'
    As you can see, the above is incorrect because it gives me a bunch of duplicate pairs. Any thoughts on how to weed those out? Thanks,

    Ben
  3. mmarovic Active Member

    I am not quite sure what are you trying to accomplish, here is my best guess:

    Code:
    select a.sku as sku1, b.sku as sku2, count(distinct a.order) as basket_count
    from [parts_orders clean] a
    join [parts_orders clean] b on a.order = b.order and a.sku < b.sku
    group by a.sku, b.sku
    You can use count(*) instead of count(distinct a.order) if it can't be multiple order items with the sam sku.

Share This Page