MySQL - Adding row total across multiple columns

Question

I have a MySQL table with about 100 columns. All columns are INT. I want to be able to select a row by its ID, and get the total number of all of the columns added together.

There are two additional considerations. The field titles vary significantly, so I would like to be able to have it work by referencing the field number, rather than the field title.

Lastly, I would like to potentially be able to specify a range, for example, add total of field 35 through field 76 where id = 50.

Is there a way to do this? Thank you very much!

This answer should be helpful [Sum values of a single row?](http://stackoverflow.com/a/2179259/1297603) — Yaroslav, Sep 29 '12 at 11:51
Can you refactor your schema? Other wise you will need to use each field name in your query. — D'Arcy Rittich, Sep 29 '12 at 11:51
Thank you both for the guidance, looks like I might have to take the scenic route and hand-jam it all in. — Brandon, Sep 29 '12 at 11:54
100 columns? Can you show us the table definition? That smells like a non-normalized table — , Sep 29 '12 at 11:58

score 1 · Accepted Answer · answered Sep 29 '12 at 11:58

You need to build dynamically a query.

First thing you need the column names (you can retrieve them automatically too, but need an extra query). Best thing is to put them in a array:

$Fields = array(
   'id', 'fld2', 'fld3', ..., 'fld99'
);

function getSumField($from, $to, $allow_null = False)
{
    GLOBAL $Fields;
    $a_sum = array();
    for($i = $from; $i <= $to; $i++)
    {
        if ($allow_null)
            $a_sum[] = "COALESCE({$Fields[$i]},0)";
        else
            $a_sum[] = $Fields[$i];
    }
    return '('.implode('+', $a_sum).')';
}

// So we want: sum of fields 17-42 of the ONE ROW where ID = 5

$fld = getSumField(17, 42);
$SQL = "SELECT $fld FROM tbl WHERE id = 5;";

// Sum of same, in rows with ids from 7 to 22
$SQL = "SELECT SUM($fld) AS total FROM tbl WHERE id BETWEEN 7 AND 22;";

score 0 · Answer 2 · answered Sep 29 '12 at 11:57

You can get a list of columns using the info_schema:

select * from information_schema.columns where table_name='table'

Using that you could build an array of columns, make your query and sum the values. Its not quite a 'one step' process but it would suffice.

MySQL - Adding row total across multiple columns

2 Answers2