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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bonmot
Forum Newbie
Posts: 1
Joined: Thu Feb 11, 2010 9:32 pm

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

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post by requinix »

MySQL doesn't do expressions in tables. Closest you can get is a view that does the math manually.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post by Eran »

You can add them manually and alias the result. Something like:
[sql]SELECT jan,feb,..., (jan+feb+...) AS totalPoints FROM ...[/sql]
Post Reply