locked
result of Divided by getting wrong RRS feed

  • 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

Answers

  • try like below

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


     


    Thanks & Regards Prasad DVR
    • Proposed as answer by Naomi N Wednesday, June 22, 2011 5:06 PM
    • Marked as answer by Kalman Toth 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 answers you, please mark it as answer..
    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 Naomi N Wednesday, June 22, 2011 5:06 PM
    • Marked as answer by Kalman Toth Monday, June 27, 2011 12:42 PM
    Wednesday, June 22, 2011 11:12 AM
  • Does this answer your question?

    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 Naomi N Wednesday, June 22, 2011 5:07 PM
    • Marked as answer by Kalman Toth 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 answers you, please mark it as answer..
    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))
    


     


    Thanks & Regards Prasad DVR
    • Proposed as answer by Naomi N Wednesday, June 22, 2011 5:06 PM
    • Marked as answer by Kalman Toth 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
  • Does this answer your question?

    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 Naomi N Wednesday, June 22, 2011 5:07 PM
    • Marked as answer by Kalman Toth Monday, June 27, 2011 12:42 PM
    Wednesday, June 22, 2011 4:13 PM