doing math in a select...

my situation is simple.

i have a database table in sql server 2000. this table has some image data (Id, fileName, Width, Height)
i would like to create a select statment that would return this data to me with 2 additional feilds. theis 2 feilds require a math function perfomed on the width and height.

i am trying to make thumbs that would be 100 width. for this i use the formula...
100 / width --> to get the scale

then i do: scaleValue * width to get the width to be 100.
i then use this scaleValue * height to maniupulate the height soto keep the height : width ration tha same and have no distortion in the image [proportions.

sql is giving me problems with this tSQL.

best i can do is...

select *, ( (100 / width) * width ) as smWidth
from tblCardImages

but the value it gives me is 0 for all images returned. looks liek the tSQL chokes oin the math and is not liking the datatype???

any ideas, or answers???
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What data type are the width and height fields?

If they are a numeric data type (int, numeric, etc) than your code should work.  If they are stored as a character data type (char, varchar, etc) then they need to be converted before performing any mathematical operation on them.

If the data types are character types then try using then following: cast(colname as int) .

select *, ( (100 / cast(width as int)) * cast(width as int)) as smWidth
from tblCardImages
kacalapyAuthor Commented:
the hight and width datatypes are both int
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
The width should always be 100, so I don't think you need a calculation for it (100 / width * width = 100, always).

So maybe something like this:

SELECT *, 100 AS smWidth, CAST(CAST(height AS DECIMAL(9, 1)) * 100.0 / CAST(width AS DECIMAL(9, 1) AS DECIMAL(9, 1)
FROM tblCardImages
kacalapyAuthor Commented:
it chokes on values that are less than 1. the 100/ with should be .25 and it returns zero all the way down
for testing i made all width values 400

even the folowing chokes and retuns zeros...
select *, ( (100 / cast(width as int))) as smWidth
from tblCardImages
Scott PletcherSenior DBACommented:

SELECT *, 100 AS smWidth, CAST(CAST(height AS DECIMAL(9, 1)) * 100.0 / CAST(width AS DECIMAL(9, 1) AS DECIMAL(9, 1) AS smHeight
FROM tblCardImages

do what scott says and cast everything as decimal.
kacalapyAuthor Commented:
soekthiung not working out in here. i am trying to make some mods now...
kacalapyAuthor Commented:
ok, i made the change to the database table to double. and my select worked out.
guess the points go to scott for pointing out the use of the dbl datatype.
kacalapyAuthor Commented:
although scott can you post the correct syntax for the casting so i can refer to it later.
Same as Scott's solution. Just missed two right parenthesis

SELECT *, 100 AS smWidth,
CAST(CAST(height AS DECIMAL(9, 1)) * 100.0 /
     CAST(width AS DECIMAL(9, 1)) AS DECIMAL(9, 1)) AS smHeight
FROM tblCardImages

Scott PletcherSenior DBACommented:
Sure, you just specify:

CAST(numeric_expression AS datatype)

For example:


CAST(100 * width / height - c + x / 14 AS DECIMAL(10,2))

Note, too, that a decimal number has a higher precedence, which in real language means that if any value is a decimal the others will be automatically cast to decimal by SQL.  So, for example, if width is an integer:

width / 100 = 0 (!), because since both are integers, SQL does integer division, so fractions are *ignored*, so 0.99 = 0

but this:

width / 100.0 = 0.99, because since 100.0 is decimal, SQL can't do integer arithmetic, and will have to force width to decimal to the calculation.

I prefer to CAST myself because:
1) it's clearer later what's happening
2) if for some reason you later remove 100.0 from the calcuation, you could accidentally go back to integer arithmetic
Hi Kacalapy,

I'm pretty sure the problem is that your columns are integers, so SQL is doing integer arithmetic.

In SQL Server "select 1/3" returns "0", while "select 1/3.0" returns the expected ".333333".

The solution is to replace the literal 100 with 100.0 to force decimals.

create table test4 ( width int, height int)

insert into test4 values (50, 80)
insert into test4 values (500, 800)

select *, 100/width scale, 100 sm_width, 100/width*height sm_height from test4

gives   width       height    scale      sm_width    sm_height  
           ----------- ----------- ----------- -----------     -----------
           50           80           2            100             160
           500         800         0             100            0

select *, 100.0/width scale, 100.0 sm_width, 100.0/width*height sm_height from test4

gives   width       height      scale                    sm_width   sm_height                    
           ----------- ----------- -----------------         --------         ----------------------------
           50           80           2.000000000000   100.0         160.000000000000
           500         800         .200000000000     100.0         160.000000000000

Your other approach is to multiply before dividing.  This still truncates to integers, but has a smaller error as the numbers are bigger.  "Select 5*5/10" gives 2 where "select 5/10*5 gives 0 but to get the correct 2.5 takes "select 5./10*5".  Thus height*100/width gives a better result than 100/width*height or height/width*100.

Regards,  Richard
Sorry Scott, I had not seen your posts when I posted the above.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.