# Bitwise Sum

• Dec 1, 2010, 12:32
StarLion
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)
• Dec 1, 2010, 12:49
r937
anything is possible with SQL :D :D

• Dec 1, 2010, 13:02
StarLion
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.
• Dec 1, 2010, 22:38
oddz
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.
• Dec 2, 2010, 12:01
StarLion
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.