Division - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Division

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-18 : 11:21:51

Hi Guys

How would u get the answer to a higher number divided by lower number to give u something apart from 0.

For example
SELECT 5/7

In theory this should equal 0.714, but when I do it in SQL it gives me 0.

Any ideas?

Thanks

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 11:31:46

I found this, hope it helps...

"What is happening is that you divide an integer value by another integer value => result is an integer."

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=195622&SiteID=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:33:33

SELECT 5.0/7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:34:16

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

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 11:35:43

try,
SELECT 5.0/7
or
SELECT 5/7.0
or
SELECT 5.0/7.0

Dividing an integer by an integer shall give you int values. Make one or both float, and it should give you float values.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-18 : 11:40:03

Thanks guys.

I was using SELECT 5/7 as an example, the actual query is:

SELECT
SUM(#Current.SalesUNits_WithoutMag)/SUM(#Previous.Prev_SalesUnits_WIthOutMag)
FROM #Current
join #Previous on #Current.subCategoryId = #Previous.subCategoryID

Giving a result of 0 when it should be something else.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:41:55

quote:
Originally posted by rcr69er

Thanks guys.

I was using SELECT 5/7 as an example, the actual query is:

SELECT
SUM(#Current.SalesUNits_WithoutMag)*1.0/SUM(#Previous.Prev_SalesUnits_WIthOutMag)
FROM #Current
join #Previous on #Current.subCategoryId = #Previous.subCategoryID

Giving a result of 0 when it should be something else.

modify as above and see. reason same as before

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 11:42:26

quote:
Originally posted by rcr69er

Thanks guys.

I was using SELECT 5/7 as an example, the actual query is:

SELECT
SUM(#Current.SalesUNits_WithoutMag)/SUM(#Previous.Prev_SalesUnits_WIthOutMag)
FROM #Current
join #Previous on #Current.subCategoryId = #Previous.subCategoryID

Giving a result of 0 when it should be something else.

use CONVERT function to convert to float.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-18 : 11:50:40

Thanks Guys!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 12:00:54

quote:
Originally posted by sakets_2000

quote:
Originally posted by rcr69er

Thanks guys.

I was using SELECT 5/7 as an example, the actual query is:

SELECT
SUM(#Current.SalesUNits_WithoutMag)/SUM(#Previous.Prev_SalesUnits_WIthOutMag)
FROM #Current
join #Previous on #Current.subCategoryId = #Previous.subCategoryID

Giving a result of 0 when it should be something else.

use CONVERT function to convert to float.

no need of explicit conversion. see my suggestion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources