Have Geek Brain

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

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 that there are standard operations for performing sums and averages in one column and multiplying the value of two columns together, but is it possible to do this across all columns in a row with a given ID? If it's not clear, I want to do something like this across rows instead of columns. Thanks to

0


a source to share


3 answers


You might be better off redesigning the table so that it doesn't have 200 columns.

eg.

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


Now you can make a request like this:

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


a source


What about:

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


The problem is you have so many columns? This solution does not handle many columns smoothly.

+2


a source


Maybe some mangled SQL should work on the contents of the entire row (I doubt it is), but you still need to specify the column names, otherwise you will have ID numbers included in the calculation.

0


a source






More articles:


All Articles