DB2/SQL equivalent of SAS's sum(of ) function

Question

SAS has a sum(of col1 - coln ) function which finds the sum of all the values from col1, col2, col3...coln. (ie, you don't have to list out all the column names, as long as they are numbered consecutively). This is a handy shortcut to find a sum of several (suitably named) variables.

Question - Is there a DB2/SQL equivalent of this? I have 50 columns (they are named col1, col2, col3....col50 and I need to find the sum of them. ie:

    select sum(col1, col2, col3,....,col50) AggregateSum
    from foo.table

If your database format use rows to store the values instead of columns, then the sum() would be easy. — Gordon Linoff
@GordonLinoff - unfortunately that can't be done since this is an account level table — appVenture

paxdiablo paxdiablo · Accepted Answer · 2015-10-09T00:36:01

No, DB2 has no such beast, at least to my knowledge. However, you can dynamically create such a query by first querying the database metadata to extract the columns for a given table.

From memory, DB2 has a sysibm.syscolumns table which basically contains the column information that you could use to construct a query on the fly.

You would first use a query like:

select column for sysibm.syscolumns
where schema = 'foo' and tablename = 'table'
and column like 'col%'

(the column names may not match exactly but, since they're not the same on the differing variants of DB2 (DB2/z, DB2/LUW, iSeries DB2, etc) anyway, that hardly matters).

Then use the results of that query to construct your actual query:

select col1+col2+...+colN AggregateSum from foo.table

where the col1+col2+...+colN bit has been built from the previous query.

If, as you mention in a comment, you only want the eighteen "highest" columns (e.g., if columns 1 thru 100 exist, you only want 83 thru 100), you can modify the first query to do that, with something like:

select column for sysibm.syscolumns
where schema = 'foo' and tablename = 'table'
and column like 'col%'
order by column desc
fetch first 18 rows only

but, in that case, you may want to call the columns col0001, col0145 and so on, or make the sorting able to handle variable width numbers.

Although it may be easier (if you can't change the column names) to get all the columns colNNN, sort them yourself by the numeric (not string) value after the col, and throw away all but the last eighteen when constructing the second query).

Both these options will return only eighteen rows maximum.

But you may also want to think, in that case, about moving the variable data to another table, if that's possible in your situation. If you ever find yourself maintaining an array within a table, it's usually better to separate that out.

So your main table would then be something like:

main_id         primary key
other_data

and your auxiliary table would be akin to:

main_id         foreign key to main(main_id)
sequence_nm
other_data
primary key (main_id, sequence_num)

That would allow you to have sparse data if needed, and also to add data without having to change the schema of the main table. The query to get the latest eighteen results would be a little more complicated but still a relatively simple join of the two tables.

DB2/SQL equivalent of SAS's sum(of ) function

1 Answers