Answers to How to sum values of multiple columns in SQL Server ( 3 )

  1. 2016-12-31 19:12
    select col1,col2,col3,col4,NVL(col1,0)+NVL(col2,0)+NVL(col3,0)+NVL(col4,0)
    (select *
    from sys.all.column
    where object_id =(select object_id from sys.all.object where name ='name of my table') 
      and name not in (list of columns that I dont need).)
       A  |  B   |  Total(col1+col2)
       1  |  2   |   3
       1  |      |   1

    Whatever columns you get, sum it and put them as seperate column in the result table.

  2. 2016-12-31 20:12

    If I understand correctly, you want to find out some columns from meta tables that you want to sum, and then sum those columns on the given table. You can use dynamic SQL to achieve this:

    create table t(a integer, b integer, c integer);
    insert into t values(1,2,3);
    declare @tab varchar(100);
    declare @sql varchar(max);
    set @sql = '';
    set @tab = 't';
    select @sql = @sql + '+' + from sys.all_columns a
    inner join 
    sys.all_objects b
    on a.object_id = b.object_id
    where = @tab
    and not in ('c');
    set @sql = 'select ' + stuff(@sql, 1, 1, '') + ' from ' + @tab;


  3. 2016-12-31 20:12

    Another option which does not require dynamic SQL, but only a CROSS APPLY or two

    Just for fun, I add Min, Max, and Avg just to illustrate... Also added a PctOfTotal or Common-Size

    Declare @YourTable table (ID int,CustName varchar(50),Sales_Jan int,Sales_Feb int,Sales_Mar int)
    Insert into @YourTable values
    (1,'John Smith',25,25,50),
    (2,'Jane Doe'  ,35,20,null)
    Select A.*
          ,PctOfTotal = Format(C.Total*1.0/Sum(C.Total) over (),'0.00%')
     From  @YourTable A
     Cross Apply (Select XMLData=cast((Select A.* For XML RAW) as xml)) B
     Cross Apply (
                    Select Total = Sum(Value)
                          ,Min   = Min(Value)
                          ,Max   = Max(Value)
                          ,Avg   = Avg(Value)
                     From  (
                            Select Value  = attr.value('.','int') 
                             From  B.XMLData.nodes('/row') as A(r)
                             Cross Apply A.r.nodes('./@*') AS B(attr)
                             Where attr.value('local-name(.)','varchar(100)') Like 'Sales_%'
                             --Or you can Exclude Specific Columns
                             --Where attr.value('local-name(.)','varchar(100)') not in ('ID','CustName')
                           ) S
                 ) C


    enter image description here

Leave a reply to - How to sum values of multiple columns in SQL Server

◀ Go back