Select a row, check if a column has the value, count the checks.

[SystemJay]

Hi!

My table looks like the following:


ID, value1, value2, value3 ..... value 12.
row: 123 1 0 0 1
row: 124 0 0 0 1
row: 125 1 1 1 1


What I need to do is to check one row at a time (with PHP) how many
number 1 entries does a single ID have in all those colums(value1-value12)
... I belive i got the PHP part right, so lets focus on SQL. (unless someone
insists telling me that too, since i'd love to see a comparison.)

Is there any more reasonable way to do this than:
SELECT value1, value2, value3, value4, value 5, value6, value7, value8, value9,value10,value11,value12 FROM ... ?

Cause what if I have ID, value1, value2 ..... value60?
I surely dont want to type SELECT value1 .... value60,
that query would look just plain silly.

Could anyone explain me what I have missed or have I? ta :thumbsup:
Cant seem to figure out how to do this reasonably. :(

[Old Pedant]

Well, you certainly *COULD* simply do
SELECT *
to get *all* the fields.

Then you php code could just use a for loop to get all the individual values. I'm not a PHP person, but I guess it would be *SOMETHING* like:

$total = 0;
for ( $i = 1; $i <= 12; ++$i ) /* and could be <= 60 of course! */
{
$total += $somePHProwvariable("value" . $i); // or square brackets???
}


However...

It may be a pain to type all those fields, but it would be *TONS* more efficient to do so.

Just do:

SELECT ID,(value1+value2+value3+....+value12) AS total

And now you don't need to do any counting at all in the PHP code.

And it may look ugly, but it's actually *MUCH* more efficient than doing it in PHP.

[SystemJay]

Alright thanks.

Now though IF i want to do this through SQL only, how would i get these counted? Since I already pictured the horror in PHP so I will do it unless you
or someone other can help me get it throgh SQL.

SELECT value_1, value_2, value_3, value_4 ... value 16 FROM ... WHERE ...

the output is naturally:
value_1 value_2 value_3 value_4 ... value 16
row: 1 1 0 1 1


Now what I need is a simple result like

total 9

where 9 is the total number of 1's counted or just summed together
since there are no other values than 0 and 1.

[Old Pedant]

I *gave* you the answer. Did you just not read my full post???

The answer is in the code line just after the words "Just do:"

That is exactly what you asked for and is the best way.

[guelphdad]

even better, normalize your data.

[SystemJay]

Woops, sorry heh.
I must have been high on life or something other to miss that.
I did read it, but I got something totally different in my mind
from it than I got when I read it the second time :p

Resolved.