Post reply

SUM NUMERIC COLUMNS

  • November 24, 2011 at 12:05 am

    #390944

    Hello,

    Can I add numeric columns together? Please refer to the following statement:

    CASE WHEN (dbo.udf_GetGenericMethod(dbo.tblAssayFlat.DataSet , dbo.tblAssayFlat.SampleID , 'LOI1000')) LIKE 'grav-d' THEN dbo.tblAssayFlat.LOI370_pct + dbo.tblAssayFlat.LOI400_pct + dbo.tblAssayFlat.LOI425_pct + dbo.tblAssayFlat.LOI650_pct ELSE dbo.tblAssayFlat.LOI650_pct END

    The ELSE part of my statement is working, but the THEN part highlighted in bold is returning NULL rather than adding the 4 numeric columns together.

    Perhaps there is something wrong with my notation, I did try and put:

    SUM(dbo.tblAssayFlat.LOI370_pct + dbo.tblAssayFlat.LOI400_pct + dbo.tblAssayFlat.LOI425_pct + dbo.tblAssayFlat.LOI650_pct) however it returns an Error Message: column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Thanks in advance for any feedback.

    Cheers, Marisa

  • November 24, 2011 at 12:11 am

    #1412566

    Are any of the numeric columns null ? If so the entire expression will return null. Unless they are defined as NOT NULL it would be wise to add coalesce(colname,0) around each one.

    Mike

  • November 24, 2011 at 12:23 am

    #1412568

    Thanks Mike, you are a star!

    😀

Viewing 3 posts - 1 through 3 (of 3 total)

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