Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014

    Unanswered: SQL to group, classify and count each group

    please look at the following data:

    party_id item date
    3506814 cap 6/19/2014
    3506814 cap 6/19/2014
    3506814 shirt 6/19/2014
    3507919 cap 6/19/2014
    3507919 shoes 6/19/2014

    for each row above, the buckets are for a given day, I want to classify each row according the rule below : basically loop over a window of data.

    if a party bought 1 from each group below, then classify it as Both or else classify it as the item itself.

    group a items (cap, whistle, scarf)
    group b items (tshirt, trouser, shoes)

    Sample output based from records above

    output 1 (I guess I need to use

    party_id item date category_grp
    3506814 cap 6/19/2014 both
    3506814 cap 6/19/2014
    3506814 shirt 6/19/2014
    3507919 cap 6/19/2014 cap
    3507919 shoes 6/19/2014 shoes

    output 2 -- count of each category (I guess I need to use PIVOT for this)

    date both cap shoes
    6/19/2014 1
    6/19/2014 1 1

    Please help me with the above scenario. Also can we do conditional counting using count(case...) over(partition by...)


  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL

    Thumbs down

    Based on the source data and your "groups", your two outputs do not make sense.
    Try explaining the rules that would produce your desired output from the source data and "groupings" you posted.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts