# question

## Sum of all columns for huge table

I need a report showing :
“Column1Name”, sum(column1)
“Column2Name”, sum(Column2)
……………………….
“Column600Name”, sum(Column600)

Where Column 1, …. Column_600 are all numeric
So in total there are 600 columns and the table is 60 GB.
I focus also on performance.

1.below works but very poor:
Select ‘Column1’ ColumnName , sum(Column1) as SumColumn from TableName union all
Select ‘Column2’ ColumnName , sum(Column2) as SumColumn from TableName union all
………………………….
Select ‘Column_N’ ColumnName , sum(Column_N) as SumColumn from TableName

2.Another idea that I consider is
Select sum(Column1) as SumColumn1,
sum(Column2) as SumColumn2,
…….
sum(Column600) as SumColumn600
from TableName
and then transposing (pivot) the columns to rows (the columnName_N can be parsed from the SumColumn_N column name)
Wondering if any better solution that 1. Or 2.

Thank you very much.

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Here is an example with two columns:

``````WITH sums AS (
SELECT SUM(column1) AS sumcolumn1, SUM(column2) AS sumcolumn2
FROM   BigTbl
)
SELECT V.Name, V.value
FROM   sums
CROSS APPLY (VALUES('Column1', sums.sumcolumn1),
('Column2', sums.sumcolumn2)) AS V(Name, Value)``````

This should be faster since it is a single scan over the table.

· 2

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your input Erland, indeed I will apply this (which is what I thought in my solution 2 from the original post. Will only need to generate this dynamic from system tables (same as Isabella shown in the portion of the code)

Thank you,

Yeah, using dynamic SQL is probably good to save your sanity here.

I skipped that part, since Isabella had already posted that part. I wanted to focus on a solution for the query itself, so that you had that going. I would recommend that you try it on, say, 10 columns and check that query plan is to your liking, before you start producing dynamic SQL.

Hi @SeHor-8331,
You can try this，replace the tablename ‘test’ ，and run

`````` Declare @sql varchar(max) = ''
declare @tablename as varchar(255) = 'test'

select @sql = @sql + 'select sum(' + c.name + ')' + c.name +  ' from [' + t.name + ']  union '
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename

select @sql = LEFT(@sql,LEN(@sql)-6)

EXEC (@sql)
``````

Best Regards,
Isabella