# Thread: BIGINT to DECIMAL and Math

1. Registered User
Join Date
Nov 2002
Location
Omaha, NE
Posts
25

## Unanswered: BIGINT to DECIMAL and Math

I have a few BIGINT columns I would like to add and then divide producing a percentage. When I do this in SQL, I receive a result of 0. When I do it with a calculator, it comes out to .97 or something. I am certain I am selecting only one record.

This returns 0

select
a / (a + b) as foo
from
bar

This returns 0.000

select
decimal(a / (a + b), 4, 3) as foo
from
bar

2. Registered User
Join Date
Nov 2002
Location
Omaha, NE
Posts
25
I knew this was something a little "Infected Mushroom" could help clear up

select
decimal(float(a) / (float(a) + float(b)), 4,3)
from
foo

returns .997

3. Registered User
Join Date
Nov 2004
Posts
1,428
Bigints do integer calculus:

3/3 = 1
6/3 = 2
5/3 = 2 the rest is lost
a bigint can't represent 0.99, only discrete values like 1 or 0

4. Registered User
Join Date
Dec 2005
Posts
273
simply convert one of the arguments ( not the result ! ) to DECIMAL:

SELECT
DECIMAL(a) / (a+b)
FROM
bar

5. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
Originally Posted by Wim
Bigints do integer calculus:

3/3 = 1
6/3 = 2
5/3 = 2 the rest is lost
a bigint can't represent 0.99, only discrete values like 1 or 0
Actuall, integer calculus means that 5/3 = 1. First you divide and then truncate fractions. There is no rounding involved.

6. Registered User
Join Date
Nov 2004
Posts
1,428