Post reply

Adding/subtracting columns to get total

  • August 16, 2012 at 8:33 am

    #280274

    here is my line of sql

    (Vehicle.value + Vehicle.Extras_Value + Vehicle.Expenses + Vehicle.Another_Tax + Vehicle.Other_Extras_Value) AS NetCost

    basically I am trying to add and subtract columns to get a total cost. The problem is that it is returning a null value for the majority of the rows. It could be a case that some value might be 0, but that should not affect the actual adding up of the fields. The third column in the calculation Vehicle.Expenses is actually a minus figure in the table which needs to be subtracted so I compensate by using the + sign. Am I missing something here.

    Thanks in advance.

  • August 16, 2012 at 8:38 am

    #1526631

    NULL+123 = NULL

    NULL + Anything = NULL.

    That's how it works. use isnull function over the fields, like that

    isnull(MyField1,0)+isnull(MyField2,0)+...

    than if the field will contain null - it will be replaced by 0.

    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

  • August 16, 2012 at 8:39 am

    #1526632

    One or more of the columns must be null.

    You can handle it by using ISNULL

    Something like below

    (ISNULL(Vehicle.value,0) + ISNULL(Vehicle.Extras_Value,0) + ISNULL(Vehicle.Expenses,0) + ISNULL(Vehicle.Another_Tax,0) + ISNULL(Vehicle.Other_Extras_Value,0)) AS NetCost

  • August 16, 2012 at 8:39 am

    #1526633

    thomasrichardson2000 (8/16/2012)

    here is my line of sql

    (Vehicle.value + Vehicle.Extras_Value + Vehicle.Expenses + Vehicle.Another_Tax + Vehicle.Other_Extras_Value) AS NetCost

    basically I am trying to add and subtract columns to get a total cost. The problem is that it is returning a null value for the majority of the rows. It could be a case that some value might be 0, but that should not affect the actual adding up of the fields. The third column in the calculation Vehicle.Expenses is actually a minus figure in the table which needs to be subtracted so I compensate by using the + sign. Am I missing something here.

    Thanks in advance.

    The rows returning NULL values - have they got one of the component values (Vehicle.value, Vehicle.Extras_Value etc) as NULLs?

    Try using the ISNULL(...) function, similar to:

    SELECT ISNULL(Vehicle.Value, 0) + ISNULL(Vehicle.Extras_Value, 0) ... AS NetCost ...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • August 16, 2012 at 9:50 am

    #1526688

    Cheers, thanks for the advice, have it calculating now. Everyday is a learning day.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply