Division in a SQL statement.

Question

Ok so my question:

I have a table itemconfig where there is lots of data concerning items stored in our warehouses. I need to select a special group of items so I can do some job related testing. So far I've been doing the math in my head as I scroll through the database but there must be an easier way.

Within itemconfig I want to specifically look at columns case_qty and pal_qty and itm_num. What I would like to do is select all itm_num where pal_qty / case_qty is greater than say 500. This would give me all itm_num instantly that are relevant to my tests. Sadly I'm not familiar with how to do this or if it's even possible.

Thanks.

Answer 1

Division is implemented in most SQL dialects: use /, like this:

select * from table
where  pal_qty / case_qty > 500

Assuming case_qty is non-negative, you can shield yourself from division by zero (and use indexes on pal_qty, if any*) by multiplying both sides by case_qty:

select * from table
where  pal_qty > 500 * case_qty

---

* Thanks Vincent Savard for this observation.