sum over multiple columns

Question

I have a table with N columns.

I would like to have the sum of each single columns:

 SELECT
    id
,   SUM(X1) as X1
,   SUM(X2) as X2
,   SUM(X3) as X3
............
FROM test

However I would prefer not to list all the columns because I have too many of them.

Is there is a way to make an overall sum for each column in SQL Server?

Answer 1

You can do this with SQL Server:

setting up a test schema

create table p (id int, x1 int, x2 int, x3 int);

insert into p values
(1,1,0,1),
(2,1,1,0),
(3,1,0,1),
(1,1,1,1);

now the SQL

declare @stmt as nvarchar(600);

set @stmt =     
(
select concat('select id,',    (
      SELECT LEFT(column_names , LEN(column_names )-1) AS column_names
    FROM information_schema.columns AS extern
    CROSS APPLY
    (
    SELECT concat('sum(',column_name , ') as ', column_name,',')
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name and column_name <> 'ID'
    FOR XML PATH('')
    ) pre_trimmed (column_names)
    where table_name = 'p' 
    GROUP BY table_name, column_names
    ),
    ' from p group by id')
    );


execute sp_executesql @stmt;

If you add a hundred other columns it should work.

You basically build a SQL Query string and then execute it.

The key here is that I am assuming that you have an ID column and then all Xn columns. So if you had another ref column, say name, that you didn't wan't summed you would change your Information_Schema query to 'and column_name not in ('id','name') etc.

If this is also Onerous, you could add a where clause in the inner select to only sum columns with Type Int.

Here is a working example