Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with multiplying and dividing in SQL

Posted on 2013-12-19
Medium Priority
233 Views
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'

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.
0
Question by:GD_GRAY
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 19

Expert Comment

ID: 39730267
Works fine for me like:

Select @iPallet / (column1 * column2) AS 'Pallets'
0

LVL 27

Expert Comment

ID: 39730279
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

Author Comment

ID: 39730351
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

Author Comment

ID: 39730376
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

LVL 11

Expert Comment

ID: 39730392
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

LVL 27

Accepted Solution

Shaun Kline earned 2000 total points
ID: 39730450
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

Author Closing Comment

ID: 39859316
Sorry it took so long...
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
###### Suggested Courses
Course of the Month6 days, 19 hours left to enroll