Primer on math functions in mysql?

bunner_bob

Can anyone point me to a tutorial or something dealing with math in mysql queries? I'm thinking along the lines of multiplying two fields of each result row together, or summing up one field in all rows.

I know there's got to be a better way than retrieving the result and looping through it in php. The mysql manual talks a bit about arithmetic and other functions, but all their examples seem to be on actual numbers, not result fields. Probably just looking in the wrong place though.

bradgrafelman

You mean the MySQL manual page for Mathematical functions ?

bunner_bob

Yeah - though the arithmetic page is more what i'm looking for.

All the examples say things like "select 2 + 4" and I want to know if I can say, "select table1.value + table2.othervalue + table3.thirdvalue AS sum". I assume I can but the manual doesn't mention retrieving values and performing calculations on them, it just gives examples with explicit numeric values.

Also, I want to be able to add up all results - for instance, I do a query for all 'amount' where userID='32' - I want to get a sum of all the amount entries - add up the "amount" field from each row in the result set and return the total. There's a way to do this, right?

bradgrafelman

bunner bob wrote:
I assume I can but

Well, instead of assuming, have you tried it?

For adding up all of the results, you would just be using the SUM() function.

bunner_bob

Lots of things "work" without them necessarily being the "right" or "best" way to do things. I certainly could try it, and it'll probably work, and then I'll roll it out to the couple of dozen odd places it could come in handy on the site I'm currently building. But I've written plenty of code I've come to regret in the past and I'd rather ask the community before trying something out of the blue like that. I'm gathering that a) there isn't the handy primer I'm looking for; and b) it works as I suggest. I suppose you could have just said that, but perhaps you feel you're teaching me a lesson, maybe for "assuming"? Apparently you're not fond of that.

Here's a tip: if you mean it with a smile, put one of those dorky but oh so effective emoticons next to it. Otherwise, don't say it.

bunner_bob

Here's a good article about expressions in MySQL.

bunner_bob

Here's a site with a bunch of good basic tutorials on MySQL Sum, Group By, Avg, etc. Much more readable than the manual.