How to get sum of multiple rows in a table dynamically

Question

I am trying to get the total sum from columns of a specific data type(money) for multiple tables in a database. Currently I am able to get the list of columns from specific tables but I am unable to get the sums from those columns.

This is what I have now

    use database 1 
Select + Column_Name
    From information_schema.columns
    Where TABLE_NAME = 'claimant'
    and data_type = 'money'

The result looks something like below

table_name	column_name
table_1	column_a
table_1	column_b
table_1	column_c

what I would like

table_name	column_name	total_sum
table_1	column_a	66.20
table_1	column_b	300.50
table_1	column_c	5389.42

update for @Squirrel Here is the code I have but it's still giving me issues with truncation.

{

declare @sql nvarchar(max);

select  @sql = 'with cte as (' + char(13)
            + 'select' + char(13)
            + string_agg(char(9) + quotename(column_name) + ' = sum(' + quotename(COLUMN_NAME) + ')', ',' + char(13)) + char(13)
            + 'from ' + max(quotename(table_name)) + char(13)
            + ')' + char(13)
            + 'select a.table_name, a.column_name, a.total_sum ' + char(13)
            + 'from   cte ' + char(13)
            + 'cross apply (' + char(13)
            + char(9) + 'values' + char(13)
            + string_agg(char(9) + '(''' + table_name + ''',''' + column_name  + ''',' + quotename(COLUMN_NAME) + ')', ',' + char(13)) + char(13)
            + ') a (table_name, column_name, total_sum)'
from   information_schema.columns AS A
INNER JOIN EDL01.STAGING.TABLE_DETAILS B
ON A.TABLE_NAME = B.DEST_TABLE_NAME
where  A.table_name = B.DEST_TABLE_NAME
and    data_type  = 'money'



print @sql
exec sp_executesql @sql 
}

below is the create table

CREATE TABLE [staging].[TABLE_DETAILS](
    [SOURCE_TABLE_NAME] [varchar](100) NULL,
    [DEST_TABLE_NAME] [varchar](100) NULL,
    [TYPE] [varchar](10) NULL,
    [PRIORITY] [int] NULL,
    [SOURCE_TABLE_DATABASE] [varchar](50) NULL,
    [SOURCE_TABLE_SCHEMA] [varchar](50) NULL,
    [DEST_TABLE_DATABASE] [varchar](50) NULL,
    [DEST_TABLE_SCHEMA] [varchar](50) NULL
) ON [PRIMARY]
GO

Below is part of the results

select a.table_name, a.column_name, a.total_sum 
from   cte 
cross apply (
    values
('PAYMENT','BILLEDAMOUNT',[BILLEDAMOUNT]),
    ('PAYMENT','AMOUNT',[AMOUNT]),
    ('SIMS_PAYMENT','CHECKAMOUNT',[CHECKAMOUNT]),
    ('BILLREVIEWHEADER','JURISDICTIONAMOUNT1',[JURISDICTIONAMOUNT1]),
    ('BILLREVIEWHEADER','JURISDICTIONAMOUNT2',[JURISDICTIONAMOUNT2]),
    ('BILLREVIE

you need to use Dynamic SQL [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) — Squirrel, Apr 22 '22 at 00:12
please show the `create table ...` for `TABLE_DETAILS ` and the output of `print @sql` — Squirrel, Apr 26 '22 at 02:27
The purpose is to do the sum for only specific tables. I am trying to get the sum from columns in multiple tables — 2tone_tony, Apr 26 '22 at 02:38
That totally changes the original scope of the question. The query that I have shown is for a single table. It does not handle multiple table. For multiple tables, the dynamic query will be much more complex. To make things easy, do a loop to iterate for each table — Squirrel, Apr 26 '22 at 02:54
Ohhh my fault I though that the code could be interchangeable in terms of adding more tables — 2tone_tony, Apr 26 '22 at 02:56
it is possible but will need changes to the query structure. Probably using union query. But if you have huge number of tables, the generated dynamic query might be too complex and huge. Loop is probably easier and keep things more manageable. See the fiddle in my updated answer — Squirrel, Apr 26 '22 at 03:13
@Squirrel I just tested your query out and it got the result I was asking. Thank you very much this is going to make things alot faster. — 2tone_tony, Apr 26 '22 at 06:00
@squirrel I am now trying to replicate similar results but in a 2012 server now. Do you know the best way to do that without the string_agg function since 2012 servers don't have the string_agg function? I tried using the 'for xml' function but it didn't work. Do you know how you would do this without the strings_agg? — 2tone_tony, Apr 29 '22 at 22:21

Squirrel · Accepted Answer · 2022-04-30T01:40:33.803

You need to form the query dynamically and then execute it using sp_executesql or exec()

Note : char(9) is tab, char(13) is carriage return. These are added to format the query so that it is readable when you print it out for verification.

declare @sql nvarchar(max);

select @sql = 'with cte as (' + char(13)
            + 'select' + char(13)
            + string_agg(char(9) + quotename(column_name) + ' = sum(' + quotename(column_name) + ')', ',' + char(13)) + char(13)
            + 'from ' + max(quotename(table_name)) + char(13)
            + ')' + char(13)
            + 'select a.table_name, a.column_name, a.total_sum ' + char(13)
            + 'from   cte ' + char(13)
            + 'cross apply (' + char(13)
            + char(9) + 'values' + char(13)
            + string_agg(char(9) + '(''' + table_name + ''', ''' + column_name + ''',' + quotename(column_name) + ')', ',' + char(13)) + char(13)
            + ') a (table_name, column_name, total_sum)'
from   information_schema.columns
where  table_name = 'table_1'
and    data_type  = 'money'

print @sql
exec sp_executesql @sql

For your sample table, the generated dynamic query is

with cte as (
select
    [column_a] = sum([column_a]),
    [column_b] = sum([column_b]),
    [column_c] = sum([column_c])
from [table_1]
)
select a.table_name, a.column_name, a.total_sum 
from   cte 
cross apply (
    values
    ('table_1', 'column_a',[column_a]),
    ('table_1', 'column_b',[column_b]),
    ('table_1', 'column_c',[column_c])
) a (table_name, column_name, total_sum)

EDIT using a loop to iterate each table. Basically it execute above query for each of the table and insert the result into a temp table

see db<>fiddle demo

for earlier SQL Server version without string_agg(), use for xml path

select @sql  = 'with cte as (' + char(13)
             + 'select' + char(13)
             + stuff
               (
                  (
                      select ',' + quotename(COLUMN_NAME) + ' = sum(' + quotename(COLUMN_NAME) + ')'
                      from   INFORMATION_SCHEMA.COLUMNS
                      where  TABLE_NAME = @table
                      and    DATA_TYPE  = 'money'
                      for xml path('')
                  ), 
                  1, 1, ''
               ) + char(13)
            + 'from ' + max(quotename(@table)) + char(13)
            + ')' + char(13)
            + 'select a.table_name, a.column_name, a.total_sum ' + char(13)
            + 'from   cte ' + char(13)
            + 'cross apply (' + char(13)
            + char(9) + 'values' + char(13)
            + stuff
            (
              (
                  select    ',' + '(''' + TABLE_NAME + ''', ''' + COLUMN_NAME + ''',' + quotename(COLUMN_NAME) + ')'
                  from   INFORMATION_SCHEMA.COLUMNS
                  where  TABLE_NAME = @table
                  and    DATA_TYPE  = 'money'
                  for xml path('')
              ),
            1, 1, ''
            )
            + ') a (table_name, column_name, total_sum)' + char(13)

Update your question with the query that you used and the table schema. — Squirrel, Apr 26 '22 at 02:02
This seems to work but I get errors where the names of the columns keep getting truncated. example ('table_c ', 'PTDRATE', [PTDRATE] ), ('table_c', 'TPDRATE', [T This gives me the following error. Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string 'T'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'T'. — 2tone_tony, Apr 26 '22 at 02:07
I added the code to my question and the schema for the table I am making a join with is a schema I called staging — 2tone_tony, Apr 26 '22 at 02:23

How to get sum of multiple rows in a table dynamically

1 Answers1