# result of Divided by getting wrong

• ### Question

• ((sum(dsr.TotalSegment)-bdg.Budget)/NULLIF(bdg.Budget,0))

here ((sum(dsr.TotalSegment)-bdg.Budget) i am getting -26290.000000

and

NULLIF(bdg.Budget,0))  is  34800   in a select query

when i doing ((sum(dsr.TotalSegment)-bdg.Budget)/NULLIF(bdg.Budget,0))

i want to get the result -75.85 something........but  i am getting wrong value that is .00000

why it is coming lie that.

If  i am put tha value as direct in query  i mean instead of ((sum(dsr.TotalSegment)-bdg.Budget)/NULLIF(bdg.Budget,0))

-26290.000000/34800  i am getting fine result.

i was run indivualy

((sum(dsr.TotalSegment)-bdg.Budget) i am getting -26290.000000

and

NULLIF(bdg.Budget,0))  is  34800

its fine

if i am going to divid it then get wrong as .00000

solution for it?

AK
Wednesday, June 22, 2011 6:54 AM

• try like below

```cast((sum(dsr.TotalSegment)-bdg.Budget) as decimal(10, 4))/cast(isnull(bdg.Budget,0) as decimal(10, 4))
```

• Proposed as answer by Wednesday, June 22, 2011 5:06 PM
• Marked as answer by Monday, June 27, 2011 12:42 PM
Wednesday, June 22, 2011 10:13 AM
• What is the data type of dsr.TotalSegment and bdg.Budget?

If both the operands for division are integers the result will show only the quotient part.

Can you try: ((sum(dsr.TotalSegment)-bdg.Budget) * 1.00 /NULLIF(bdg.Budget,0)) ?

The simpler the solution the stronger it is
If this post is useful, please vote it as useful..

Try multiplicating with 100.0 instead to get the value OP requests.
• Proposed as answer by Wednesday, June 22, 2011 5:06 PM
• Marked as answer by Monday, June 27, 2011 12:42 PM
Wednesday, June 22, 2011 11:12 AM

```create table #t
(TotalSegment int
,BudgetInt  int
,BudgetDec  decimal(19,6)
)
insert into #t values (8510,34800,34800)

select CAST( (TotalSegment-BudgetInt)/NULLIF(BudgetInt,0) AS decimal(19,6))
,   CAST( (TotalSegment-BudgetDec)/NULLIF(BudgetDec,0) AS decimal(19,6))
from #t

drop table #t

--------------------------------------- ---------------------------------------
0.000000                -0.755460

```

Gert-Jan
• Proposed as answer by Wednesday, June 22, 2011 5:07 PM
• Marked as answer by Monday, June 27, 2011 12:42 PM
Wednesday, June 22, 2011 4:13 PM

### All replies

• Always provide your Table's DDL involved in your SQL query or script.

Provide some test data and expected o/p.

What SQL Server version you are using?

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011
Wednesday, June 22, 2011 7:23 AM
• I agree with manoj to provide us sample data and create table statement to check.
BTW, instead of NULLIF, can you try ISNULL?

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter
Wednesday, June 22, 2011 7:58 AM
• What is the data type of dsr.TotalSegment and bdg.Budget?

If both the operands for division are integers the result will show only the quotient part.

Can you try: ((sum(dsr.TotalSegment)-bdg.Budget) * 1.00 /NULLIF(bdg.Budget,0)) ?

The simpler the solution the stronger it is
If this post is useful, please vote it as useful..
Wednesday, June 22, 2011 9:48 AM
• try like below

```cast((sum(dsr.TotalSegment)-bdg.Budget) as decimal(10, 4))/cast(isnull(bdg.Budget,0) as decimal(10, 4))
```

• Proposed as answer by Wednesday, June 22, 2011 5:06 PM
• Marked as answer by Monday, June 27, 2011 12:42 PM
Wednesday, June 22, 2011 10:13 AM
• ((sum(dsr.TotalSegment)-bdg.Budget)/NULLIF(bdg.Budget,0))

here ((sum(dsr.TotalSegment)-bdg.Budget) i am getting -26290.000000

and

NULLIF(bdg.Budget,0))  is  34800   in a select query

when i doing ((sum(dsr.TotalSegment)-bdg.Budget)/NULLIF(bdg.Budget,0))

i want to get the result -75.85 something........but  i am getting wrong value that is .00000

why it is coming lie that.

If  i am put tha value as direct in query  i mean instead of ((sum(dsr.TotalSegment)-bdg.Budget)/NULLIF(bdg.Budget,0))

-26290.000000/34800  i am getting fine result.

i was run indivualy

((sum(dsr.TotalSegment)-bdg.Budget) i am getting -26290.000000

and

NULLIF(bdg.Budget,0))  is  34800

its fine

if i am going to divid it then get wrong as .00000

solution for it?

AK

Hi Mujeeb85a

I'm sorry if I'm wrong, but there is a typo mistake in your statement that means when I ran the same expresion directly I'm getting the value, like:

select (-26290.000000/34800) -- -0.755459770114

but not -75.85 (expected value).

I think the issues lies in the expression: ((sum(dsr.TotalSegment)-bdg.Budget) itself. Please select each expressions individualy and check the result values, like:

select sum(dsr.TotalSegment) as TotalSegment, bdg.Budget from TableName  ....

Let me know if you are getting the same issue.

Kiran
Wednesday, June 22, 2011 11:31 AM

```create table #t
(TotalSegment int
,BudgetInt  int
,BudgetDec  decimal(19,6)
)
insert into #t values (8510,34800,34800)

select CAST( (TotalSegment-BudgetInt)/NULLIF(BudgetInt,0) AS decimal(19,6))
,   CAST( (TotalSegment-BudgetDec)/NULLIF(BudgetDec,0) AS decimal(19,6))
from #t

drop table #t

--------------------------------------- ---------------------------------------
0.000000                -0.755460

```

Gert-Jan
• Proposed as answer by Wednesday, June 22, 2011 5:07 PM
• Marked as answer by Monday, June 27, 2011 12:42 PM
Wednesday, June 22, 2011 4:13 PM