Perform sum, average, and multiplication across all columns in a table?

Question

I have a table that looks like this:

id    test1    test2    test3    test4 ... test200
1      90       87       85        86        70
2      100      95       83        92        80
.
.
18000

I know there are standard operations to perform sums and averages on a single column and multiply the value of two columns together but is it possible to do it across all columns in a row with a given id? If its not clear, I want to do something like this across rows instead of across columns. Thanks

Answer 1

How about:

select id, sum(test1 + test2 + ...) as summation
group by id

Is the problem that you have so many columns? This solution doesn't handle many columns smoothly.

Answer 2

There might be some contorted SQL to work on the contents of the whole row (I doubt that), but you still have to specify the column names, otherwise you'd have the ID numbers included in the calculation.

Answer 3

You might be better off redesigning the table so that it does not have 200 columns.

e.g.

Id  testnum  score
1    1        90
1    2        87
...
2    1        100
2    2        95
...
180000

Now you can do a query like this:

select sum(score) as totalscore
from mynewtable
where id=1