SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Bitwise Sum

  1. #1
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)

    Bitwise Sum

    Is it possible to do a bitwise SUM()?

    IE:
    user_id key
    1 1 (001)
    1 2 (010)
    2 4 (100)
    3 2 (010)


    SELECT user_id SUM()... FROM table GROUP BY user_id;

    user_id SUM()
    1 3 (011)
    2 4 (100)
    3 2 (010)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    anything is possible with SQL


    could you please explain a bit more about what you're doing?

    also, please do a SHOW CREATE TABLE for your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Inheriting someone else's table lol

    the schema is:
    products
    -------
    product_id int(2) PRIMARY,
    ... (unnecessary fields)
    product_purchase_type int(2) (1-19, plus several NULL entries. NOT an index.)
    product_key tinyint(1) (effectively an enum(1,2,3,4), bitwise 3-bit key. This field is -not- unique.)

    purchases
    ----------
    purchase_id bigint(9) PRIMARY AUTO_INCREMENT (sigh. horrible design)
    purchase_type_id int(2) (FK: products.product_purchase_type)
    user_id bigint(6) (FK: users.user_id)

    Given a specific user_id value, i'm trying to determine all the products purchased. left joining purchases and products and then grouping on user_id gives me the groups... but SUM of 3 and 2 and 1 and 4 both = 5...and given the prior programmer's sloppyness, i cant guarantee there isnt a 3 and 2 for the same user in the table.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    So how does one determine the product a user purchased? The puchase table makes little sense considering the column product_purchase_type inside products is not unique. Am I missing something? I don't quite understand the decision not to use product_id as the foreign key and that is probably a significant part to solving this with the given tables.

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Right products.product_purchase_type is not mysql-enforced-unique (as it allows Null), but among values specified in the table, it is 'unique'; product_key is not.

    Think of it this way: There are 3 products. (1,2,4, bitwise).
    A person could go to the website, and purchase products 1&2 as a bundle. This would have product_purchase_type of say.. 6 (which comes from the code as the product ID passed into clickbank's service for payment), and product_key of 3 (001 + 010).
    However, if said person clicked on a link in the email newsletter, they still might be buying products 1&2 as a bundle. This time, product_purchase_type of 9, and product_key of 3.

    Again, not my table design, i'm just inheriting it.


Bookmarks

Posting Permissions

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