Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
T-sql query for finding count for no of transaction bundles

Proposed Answer T-sql query for finding count for no of transaction bundles

  • Wednesday, March 06, 2013 11:17 AM
     
     
    Sign In to Vote
    0

    HI ALL,

    SAMPLE TABLE

    TID  ITEMS

    1 A,B

    2 A,B,C

    3 B,C

    I WANT OUTPUT AS

    ITEMS  no_OF_TRANSACTIONS

    A,B 2

    B,C 2

    A,B,C 1

    PLEASE HELP ME FOR FINDING SOLUTION

     

All Replies

  • Wednesday, March 06, 2013 11:27 AM
     
     Proposed Answer
    Sign In to Vote
    0

    Storing data as comma-separated list in a table column is an utterly bad idea; you should store each value individually in a subtable. A basic principle in a relational database is "no repeating groups" and if you break this rule, you will get hurt.

    I can see the pattern in the desired output, but why not entries for A, B and C alone in the output?

    What is your practical use case for this exercise?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Wednesday, March 06, 2013 12:40 PM
     
     
    Sign In to Vote
    0

    the practical scenario is shopping cart

    the clients want to know which group of products sold together with no of trasactions

    A,B,C indicates three products sold together one time

     
  • Wednesday, March 06, 2013 12:44 PM
     
     
    Sign In to Vote
    0

    otherwise we can  use the table like this

    tid items

    1 A

    1 B

    2 A

    2 B

    2 C

    3 B

    3 C

    OUTPUT

    PRODUCTS NO_OF_TRANSACTIONS_TOGETHER

    A,B 2

    B,C 2

    A,B,C 1

     
  • Wednesday, March 06, 2013 10:56 PM
     
     
    Sign In to Vote
    0

    Why would there not be a row with A,C = 1? I mean, you count B,C twice, and one of them is then shopping cart has A,B,C.

    If you want any possible pair, triplet etc, this can explode quite heavily. If the shopping-cart has 12 items, that is over 1000 possible combinations.

    On the other hand, if you only want the disctinct combinations, so that A,B and B,C only gets a count of 1, it's simple. Probably also if you restrict yourself to pairs or triplets. Can you check the requirements in more detail?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Thursday, March 07, 2013 11:00 PM
     
     
    Sign In to Vote
    0

    The more I have thought about it, I don't think writing your of T-SQL code is the right thing, for two reasons:

    1) As the shopping-cart grows in size, you will drown in combinations - where of which many are not of interesting. The code and the amount of data you massage will explode.
    2) Not surprisingly, there are already solutions out there.

    Data Mining is certainly one option. An MVP colleauge was kind to point me to this introductory video for Microsoft's Shopping Basket Analysis Tool.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Sunday, March 10, 2013 3:21 AM
    Moderator
     
     
    Sign In to Vote
    0
    Where is the link to the video?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog