mysql dividing all columns by values from one column (for vector calculation etc)

i have a table of this general format. it was generated via pivoting, so the number of columns is not fixed.

id  c1  c2...          total
10  0   2   1   1   0   4
9   0   1   0   1   0   2
8   1   2   0   0   0   3
7   0   0   0   1   0   1
6   0   1   0   1   1   3
5   1   0   0   1   2   4
4   0   1   1   0   0   2
3   0   3   0   1   1   5
2   2   2   2   0   0   6
1   1   0   1   0   0   2

what i need, is to take the "total col" (last from left), and divide each one of the {c1, c2, c3....} columns by their respective total... for instance, if row 10, c2=2, then c2/total = 2/4 =0.5

just to emphasize, the number of cols. is not fixed. this is a sample table.

is it possible do to only via mysql, or is an external script needed?

many thanks

EDIT TO CLARIFY:

my inital data, pre-pivoting, looks like this:

2   2
8   1
2   2
1   5
3   1
9   1
5   3
4   1
1   2
10  5
6   4
4   5
5   2
10  3
5   4
3   1
6   1
6   3
3   4
3   1
5   4
7   3
2   5
10  1
9   3

where the first col is "id", second is "c". as shown, it needs to be transformed into a contingency table of sort. where each id has a count for each "c" {c1,c2,c3...}

is there an efficient way to code this data into a th开发者_开发百科e format @bobwienholt mentioned below? (i'm new to mysql, in fact i taught it to myself today for the pivoting. apologies if this is trivial).

---

If I were you, I would structure my table as follows:

CREATE TABLE data ( row INT, col INT, value INT );

Then you can do this:

SELECT d.row, d.col, d.value/t.total
FROM (
  SELECT row, SUM(value) as total
  FROM data
  GROUP BY row;
) t INNER JOIN data d
ON d.row = t.row
ORDER BY row, col;

It would work for any number of "rows" and "columns".

---

Ok, based on your edit... I would just import the data as is. So you would create a table like this:

CREATE TABLE data ( id INT, c INT);

Then you could import your data using LOAD DATA INFILE. You should consult the MySQL docs to learn how to use that.

Then, you would get all your c1, c2, etc counts like this:

SELECT id, c, COUNT(1) as num
FROM data
GROUP BY id, c;

That would yield results like (based on your sample data):

id       c        num
1        2        1
1        5        1
2        2        2
2        5        1
3        1        3

So, basically for id 3, c1 = 3... for id 2 c2=2, etc.

Your total column would be:

SELECT id, SUM(num) as total
FROM (
  SELECT id, c, COUNT(1) as num
  FROM data
  GROUP BY id, c
) x
GROUP BY id;

In this scenario, you wouldn't have to pivot your data.