calculated fields in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
php4user2007
Forum Newbie
Posts: 12
Joined: Mon Apr 02, 2007 4:12 am

calculated fields in mysql

Post by php4user2007 »

Hi,

I'm trying to create some basic customer account functionality (not enough to warrant an open-source shopping cart solution) and I was wondering if MySQL can have automtically calculated fields.

For instance for each customer account, if I have a few fields with the cost of the customer's purchased items and a few fields with payments the customer made, can I have a field in MySQL that will always contain the balance between the two stored in the database. That way whenever I want to echo or display the balance to the customer, I don't need to extract and then sum up and subtract the indidividual items in my php code.

MS Access easily allows for such functionality, so I assume MySQL has a similar feature.??

thanks

p
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

For a table to be normalized, all the values in a table should only depend on the key and not on a non-key field. So having a running total that is based on the sum of other values in that table is a violation, not to mention a maintenance headache.

The solution is to use SQL aggregate functions like

Code: Select all

SELECT SUM(i.itemPrice)
FROM tblCust c 
LEFT JOIN tblItems i
ON c.custID = i.custID
GROUP BY c.custID
You could also create a view or a stored procedure. I believe the method you describe in access uses aggregate functions in a query as above.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

it is bad practice to store calculated values in an operational database (this is different from data warehouses/data marts).

You dont need to do the summing/adding etc in PHP, do it as part of your query

for example

Code: Select all

SELECT customerid, SUM(items_purchased)-SUM(payments_made) AS balance FROM mytable GROUP BY customerid
Post Reply