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!

Can you refactor your schema? Other wise you will need to use each field name in your query. — D'Arcy Rittich
– D'Arcy Rittich, Commented Sep 29, 2012 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
– Brandon, Commented Sep 29, 2012 at 11:54
100 columns? Can you show us the table definition? That smells like a non-normalized table — user330315
– user330315, Commented Sep 29, 2012 at 11:58

LSerni · Accepted Answer · 2012-09-29 11:58:02Z

1

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;";

answered Sep 29, 2012 at 11:58

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ethrbunny · Accepted Answer · 2012-09-29 11:57:10Z

0

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.

answered Sep 29, 2012 at 11:57

ethrbunny

10.5k9 gold badges77 silver badges145 bronze badges

Collectives™ on Stack Overflow

MySQL - Adding row total across multiple columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related