CODEWITHSUNDEEP
sqlsql-server
Justin Ofodile
Justin Ofodile

Reputation: 21

How to sum values of multiple columns in SQL Server

SELECT 
    name 
FROM 
    sys.all.column 
WHERE object_id = (SELECT object_id 
                   FROM sys.all_objects 
                   WHERE name ='name of my table' and type = 'TT') 
  AND name NOT IN (list of columns that I don't need)

How do I sum all the values of the returned columns from the preceding SQL query?

Upvotes: 2

Views: 5322

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81940

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.*
      ,C.*
      ,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

Returns

enter image description here

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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 + '+' + a.name from sys.all_columns a
inner join 
sys.all_objects b
on a.object_id = b.object_id
where b.name = @tab
and a.name not in ('c');

set @sql = 'select ' + stuff(@sql, 1, 1, '') + ' from ' + @tab;

exec(@sql);

Produces:

3

Upvotes: 1

user2736738
user2736738

Reputation: 30926

select col1,col2,col3,col4,NVL(col1,0)+NVL(col2,0)+NVL(col3,0)+NVL(col4,0)
from
(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.

Upvotes: 0

Related Questions