How to build a query to divide a field based on another field?

Question

I want to get cases of wine where each bottle is between $4 and $8. The costs in the products table are all in cases of 12. Easy huh?

SELECT `id` 
  FROM `products`
 WHERE (`cost` / 12) > 4
   AND (`cost` / 12) < 8

Now, they are beginning to sell cases of 6. Assume that this is defined only in the title string with 6 pack.

How do I write my query so it selects all cases of wine where price / 12 is in range, or if the title string contains 6 pack, divide by 6 instead.

Is this possible in one query?

Thanks

Answer 1

Yes, but consider adding a "percase" field. String comparisons are not the fastest operations around.

`cost`/IF(`title` LIKE '%6 pack%', 6, 12)

Answer 2

Hi,

Depending on you database server, you might be able to use a regular expression or string function to look for a certain pattern and pull out the quantity. Or you might want to do some other processing on the table externally to add a new column using java or .net or some other scripting language.

SELECT 
 `id`,
 SomeRegexFunctionOrStringFunction(description) quantity 
FROM `products`
 WHERE (`cost` / quantity) > 4
   AND (`cost` / quantity) < 8

Enjoy!