question

SeHor-8331 avatar image
0 Votes"
SeHor-8331 asked ErlandSommarskog commented

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.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

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

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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
5 |1600 characters needed characters left characters exceeded

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,

0 Votes 0 ·

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.

0 Votes 0 ·
Isabellaz-1451 avatar image
0 Votes"
Isabellaz-1451 answered SeHor-8331 commented

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


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.



· 1
5 |1600 characters needed characters left characters exceeded

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

Thank you Isabella, your answer does generate the SQL statement dynamically which is a plus. However this will still scan the table many times same as I shown in my original post. Based on this I will apply Erland's solution, which I will have to generate dynamically starting from the systems tables (same idea as you shown here).

Thank you for your input.

1 Vote 1 ·