how to perform division on two integer column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to perform division on two integer column

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-04-17 : 03:00:01

Hi all,
I have a declared a table variable with two integer column.
I am trying to perform division operation on them but I am getting result as "0"

can anybody help me to solve this

regards
Durgesh J

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-17 : 03:04:08

Hi,
Please post ur sample data

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-04-17 : 03:08:10

hi,

DECLARE @TBL TABLE(ID INT IDENTITY(1,1),[DESCRIPTION] VARCHAR(100),NO_OF_ITEMS INT,TOTAL_ITEMS INT)
insert into @tbl select 'aa',2,5729;
insert into @tbl select 'bb',3,5729;
insert into @tbl select 'cc',18,5729;
insert into @tbl select 'dd',5706,5729;
SELECT NO_OF_ITEMS/TOTAL_ITEMS FROM @TBL

--output
0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 03:26:14

SELECT 1.0E * NO_OF_ITEMS/TOTAL_ITEMS FROM @TBL

E 12°55'05.63"
N 56°04'39.26"

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-04-17 : 03:34:27

Hi Peso,
It is not giving the expected result
wherein I am getting the result as

3.4910106475824753E-4
5.2365159713737132E-4
3.1419095828242277E-3
0.9959853377552802

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 03:43:59

You haven't told us what your expected output is.

Here is another guess.
SELECT 1.0E * SUM(NO_OF_ITEMS) / SUM(TOTAL_ITEMS) FROM @TBL

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 05:00:13

Here is the reason
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-04-17 : 05:09:50

hi peso,
I have tried the following query and got the output

SELECT CAST(NO_OF_ITEMS AS NUMERIC)/TOTAL_ITEMS FROM @TBL

thanks for u r help

regards
Durgesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 05:14:17

And what is the difference with the "1.0E *" suggestion?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources