How to dynamically calculate sum of multiple unknown columns in sql?

Question

I have multiple columns with some amount in a table and I want to show the total of all those amounts in the last Total column. I have a table in sql which looks somewhat like this,

A_Amt  B_Amt  C_Amt  D_Amt  E_Amt  F_Amt ... Total
------------------------------------------------
 15     20     25     30     35    40

I do not want to do sum(col1 + col2 + ..) like this because there are many columns. Is there another way to get the sum using a where clause like **where columnname.name like '%Amt%'**?

I am trying to use UNPIVOT and I came up with the following code but it is not working,

select Product_ID, Amount
FROM Products
unpivot
(Amount for Product_ID in 
(select c.name + '' from syscolumns c(nolock) where (id = 
(select id from dbo.sysobjects where name = 'Products'))
and (c.name like '%Amt%')))

Any ideas will be helpful.

You could look into using UNPIVOT. But personally, I'd go with the (Col1 + Col2 +...) solution, which will probably perform the best, and I'm not lazy. — Tab Alleman, Dec 19 '14 at 19:09
Your table has repeating groups and therefore is de-normalized. Sorry, querying it is often going to suck, and there's no way around it. It's the fault of your schema. — Bacon Bits, Dec 19 '14 at 19:13
Okay...how many columns do you have ? Would it be okay if the sum(.....) query was automatically generated for you ? — Erran Morad, Dec 19 '14 at 19:13
I have 54 columns and yes it would be okay if the sum query was automatically generated. — Arpita, Dec 19 '14 at 19:22
simply do `SELECT A_Amt + B_Amt + C_Amt + D_Amt + E_Amt + F_Amt` — M.Ali, Dec 19 '14 at 19:22
@Arpita - Good. Now, would you be willing to get the answer yourself if I gave you the main steps to get what you need ? — Erran Morad, Dec 19 '14 at 19:42

score 3 · Accepted Answer · edited May 23 '17 at 12:30

The dyanamic UNPIVOT solution would need to use one of the GROUP_CONCAT hacks to scrape the column names you've obtained via sys.syscolumns into a comma delimited string before entering into the UNPIVOT.

But if blinding performance isn't a requirement, how about invoking xquery to do this in Xml? It's much simpler:

declare @xmlResult xml=
(   
    select  * 
    from    Foo 
    for xml PATH
);

SELECT Nodes.node.value('sum(*[contains(local-name(.), "_Amt")])', 'decimal(15,2)') AS Total
  FROM
  @xmlResult.nodes('//row') as Nodes(node);

With a SqlFiddle here

Basically, after rolling up the whole table into Xml, it then iterates the rows (//row), evaluating the sum of all the elements with a name which contains _Amt.

The real benefit is that you don't need to bother figuring out the actual column names at all - that'll be deferred into the xpath.

score 1 · Answer 2 · answered Dec 19 '14 at 19:49

This is an unusual table layout, and I don't think there's a perfect shortcut for what you want to do. One option is to push the addition to the table level with a computed column:

ALTER TABLE mytable ADD Total AS A_Amt + B_Amt + ... + zz_Amt;

After the initial trouble of setting up the computed column, your future queries get a lot easier:

SELECT Total FROM myTable

Erran Morad · Answer 3 · 2014-12-21T07:54:16.083

I'll be using Dynamic SQL. Google a simple example for this.

1 - Generate a string with all column names - c1+c2+c3...

2 - Create a string with full sql query, using 1 also.

3 - Execute string in 2 as dynamic sql.

I'll combine 1 and 2 though.

declare @sql  varchar(max)
set @sql = 'SELECT ' 

declare @tbl varchar(100)
set @tbl = 'temp' -- put your table name here

SELECT @sql = @sql + COLUMN_NAME + '+'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tbl 
-- AND TABLE_SCHEMA='junk'

set @sql = LEFT(@sql, LEN(@sql) - 1)
set @sql = @sql + ' FROM ' + @tbl

select @sql

-- dynamic sql

exec(@sql)

How to dynamically calculate sum of multiple unknown columns in sql?

3 Answers3