Help with multiplying and dividing in SQL

I would like help with a select statement I can't seem to get to work.

I have a column1 with the total case count = 10
I have a column2 with the total amount of space a single case will occupy = 5 cubes

select column1 * column2 AS 'Cubes'....... this will return {50}

What I need to do is return the number of pallets it will take to put the total number of case's on.

I know I need to declare the cube amount for one pallet

DECLARE @iPallet int
SET @iPallet = '112' <-- Each pallet holds 112 cubes-->

Here is where I have to guess.

Select @iPallet / column1 * column2 AS 'Pallets'

As it is I get no results and the message reads. "Divide by zero error encountered"

Yet if I use:

Select @iPallet/column2 AS 'Pallets'

I get results (not what it needs to be, but I get them) and the message still reads "Divide by zero error encountered"

Can some one please tell me how to do this.
GD_GRAYAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
If you need to know how many pallets are needed for X number of cubes and you know Y cubes fit on a pallet, you need to divide the total number of cubes by the "cubes per pallet" value. This is where understanding how Units need to cancel out helps:

(x cases * y cubes/case) / (z cubes/pallet)

Also, since your columns are numeric values (you can have a partial cube?) you use the ROUND function to handle the decimals.
0
 
jss1199Commented:
Works fine for me like:

Select @iPallet / (column1 * column2) AS 'Pallets'
0
 
Shaun KlineLead Software EngineerCommented:
Sounds like a homework question but...

SELECT (column1 * column2) / <cubes per pallet> + CASE WHEN (column1 * column2) % <cubes per pallet> <> 0 THEN 1 ELSE 0 END

The part after the plus handles cubes that do not fill a complete pallet.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GD_GRAYAuthor Commented:
Shaun_Kline:

Doing it that way I get results, but I need it to divide first and if I try:

SELECT @iPallet / (column1 * column2)  + CASE WHEN (column1 * column2) % @iPallet <> 0 THEN 1 ELSE 0 END

It acts as before and returns nothing.
0
 
GD_GRAYAuthor Commented:
I take that back. I just need to slide the decimal to the right and remove all the extra

1.02563562356  should be 0.126

can that be done as well ?
0
 
John_VidmarCommented:
integer <action> integer = integer

examples:
      1/2 = 0
      5/2 = 2

if you don't want that to happen then change @iPallet to numeric instead of integer.

Also, don't divide by zero:
      something / NULLIF(blah, 0)
0
 
GD_GRAYAuthor Commented:
Sorry it took so long...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.