Multiple Column Summation

Question

I have a table with N columns.

I would like to have the sum of each individual column:

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 a way to make a total amount for each column in SQL Server?

Answer 1

You can do this using SQL Server:

test setup

 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 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, they should work.

Basically you create a SQL Query string and execute it.

The key point here is that I assume that you have an ID column and then all the Xn columns. Therefore, if you have another ref column, say a name that you did not summarize, you will change your Information_Schema query to 'and column_name not in ('id','name') , etc.

If it is also uniform, you can add a where clause in the inner select to only sum columns of type Int.

Here is a working example

Answer 2

you ask for it

 x1 x2 x3 2 3 4 2 3 4 4 6 8 --result 

then try this code

  SELECT SUM(salary) as 'X1', SUM(age) as 'X2', SUM(mark) as 'X3' FROM tsum