Adding/subtracting columns to get total

  • 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.

  • 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

  • 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

  • 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

  • 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