Page 1 of 1

Adding Across Row, "Sum" adds down --- how to add across?

Posted: Thu Feb 11, 2010 9:55 pm
by bonmot
Hi. I'm getting stuck on what I thought would be a pretty easy task but this is me so ... HELP!

I have a table called tblUsers.
I have 12 columns one for each month that contains each users' points for the month.
I have another column called totalPoints. I want that column to contain the sum of each users' points.

I figured out that sum will add down a column. How do I add across a row? For each userName?
I can use PHP or perhaps there's a field setting in MySQL that will add the values??

tblUsers:
________________________________________________________________________________________________________
userName ...... | ..... Jan ..... | ..... Feb ..... | ..... Mar ..... | ..... Apr (etc) ..... | ..... totalPoints
-----------------------------------------------------------------------------------------------------------------------
Ann ............. | ..... 250 ..... | ..... 225 ..... | ..... 275 ..... | ..... 300 ............. | ..... (should be) 1050
Bob ............. | ..... 300 ..... | ..... 175 ..... | ..... 0 ......... | ..... 250 ............. | ..... (should be) 725
Cal .............. | ..... 175 ..... | ..... 250 ..... | ..... 225 ..... | ..... 275 .............. | ..... (should be) 925

So... what is the PHP code to add Ann's 250+225+275+300 (etc); put the result in "totalPoints" column
and then repeat for each userName?

Any suggestions would be greatly appreciated.

Re: Adding Across Row, "Sum" adds down --- how to add across?

Posted: Fri Feb 12, 2010 5:09 am
by requinix
MySQL doesn't do expressions in tables. Closest you can get is a view that does the math manually.

Re: Adding Across Row, "Sum" adds down --- how to add across?

Posted: Fri Feb 12, 2010 9:38 am
by Eran
You can add them manually and alias the result. Something like:
[sql]SELECT jan,feb,..., (jan+feb+...) AS totalPoints FROM ...[/sql]