can I have a feild in a mysql table that will always contain the current average of two other fields in that table?

As far as I know, you can't do this with MySQL alone.
But you can use PHP + MySQL queries to get the average of what you want and store that average in a table.

But as far as I know, MySQL can't do this directly.

Chad

No, you can't use a database like Excel if that's what you're thinking. A database is 'static' if you like, the data you enter cannot effect data in another field and row.

so realisticly I'd update that column when I update one or both of the other rows then?

It would be completely possible to write a PHP extension to use an Excel datasheet, right?



Yes, each time you update one or both rows, the column needs to be updated, as you said.

Chad

ok so here's a different question

can a query look like this



or is that invalid syntax?

You can do math inside a query, but I am not sure if that one is possible.
Before actually writing a script using it, try it in PHPMyAdmin for syntax.

Chad

ouch, to make it work how I want, I had to use this query

Rather than actually storing the data, would it be more efficient to do the math on the actual query when you NEED the data?

Such as:


...and then your WHERE clause etc, etc...

-edit-
...forgot to close the code tag.

not really as I plan to use that data in other ways (such as ordering by it when selecting)

You can still order by it during the select, but it would be faster if the field were not calculated during the select. Just figured it'd be easier. I just hope that those two fields don't change often on you.