Sum of all columns for huge table

Question

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.

Answer 1

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.

Answer 2

Hi @SeHor ,
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

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.