Performing subtraction between fields

Hello,

For simplicity's sake, let's say I have three fields, A, B and C, all
of which are integers. I'd like the value of C to be equal to A less B
(A-B). Is there a way I can perform this calculation? I'm guessing it
would happen when I INSERT a row and specify the values for A and B.
Feel free to direct me to the fine manual I should have read.

Thank you.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Performing subtraction between fields

On 21/08/2010 07:25, b@qxhp.com wrote:
> Hello,
>
> For simplicity's sake, let's say I have three fields, A, B and C, all
> of which are integers. I'd like the value of C to be equal to A less B
> (A-B). Is there a way I can perform this calculation? I'm guessing it
> would happen when I INSERT a row and specify the values for A and B.
> Feel free to direct me to the fine manual I should have read.

Given two variables, $a and $b:

INSERT INTO mytable SET A = $a, B = $b, C = $a - $b

or

INSERT INTO mytable (A, B, C) VALUES ($a, $b, $a - $b)

or, if you've previously inserted A and B:

UPDATE mytable SET C = A - B

http://dev.mysql.com/doc/refman/5.1/en/numeric-functions.htm l

Simples :-)

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performing subtraction between fields

--0022158c1099689b9a048e53134c
Content-Type: text/plain; charset=ISO-8859-1

Do you have a really good reason to store a computed value? It's only useful
if you will perform a search on the column, else you could just do the
subtraction when you SELECT columns A and B.

- Ashish

On Sat, Aug 21, 2010 at 11:55 AM, wrote:

> Hello,
>
> For simplicity's sake, let's say I have three fields, A, B and C, all
> of which are integers. I'd like the value of C to be equal to A less B
> (A-B). Is there a way I can perform this calculation? I'm guessing it
> would happen when I INSERT a row and specify the values for A and B.
> Feel free to direct me to the fine manual I should have read.
>
> Thank you.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=ashish.mukherjee@gmail.co m
>
>

--0022158c1099689b9a048e53134c--

Re: Performing subtraction between fields

Ashish, Mark and off-list responders,

Thanks!

Ashish, is there a really good general reason not to store a computed
value? Searches will be done on this column. Searches like 'Please send m=
e
the rows with the ten highest values of C.'

> Do you have a really good reason to store a computed value? It's only
> useful
> if you will perform a search on the column, else you could just do the
> subtraction when you SELECT columns A and B.
>
> - Ashish
>
> On Sat, Aug 21, 2010 at 11:55 AM, wrote:
>
>> Hello,
>>
>> For simplicity's sake, let's say I have three fields, A, B and C, all
>> of which are integers. I'd like the value of C to be equal to A less B
>> (A-B). Is there a way I can perform this calculation? I'm guessing it
>> would happen when I INSERT a row and specify the values for A and B.
>> Feel free to direct me to the fine manual I should have read.
>>
>> Thank you.
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Dashish.mukherjee@gmail. com
>>
>>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Performing subtraction between fields

--002215048ac75a717a048e53c4a7
Content-Type: text/plain; charset=ISO-8859-1

Well, since you are searching that column, it's probably a good idea.

Possible scenarios for not storing computed values -

1) Data integrity - say, the columns A and B are updated but C does not get
updated, resulting in an anomalous situation
2) Data-set is large and the extra column leads to additional bloat and
you are not searching the column C.

Then, selectivity is also a factor. Best way is to do a EXPLAIN for your
query and see the gain you get from having the additional column vs not.
It's hard to say what's right for you without knowing more about the
data-set.

Regards,
Ashish

On Sat, Aug 21, 2010 at 4:32 PM, Ashley Stars wrote:

> Ashish, Mark and off-list responders,
>
> Thanks!
>
> Ashish, is there a really good general reason not to store a computed
> value? Searches will be done on this column. Searches like 'Please send me
> the rows with the ten highest values of C.'
>
> > Do you have a really good reason to store a computed value? It's only
> > useful
> > if you will perform a search on the column, else you could just do the
> > subtraction when you SELECT columns A and B.
> >
> > - Ashish
> >
> > On Sat, Aug 21, 2010 at 11:55 AM, wrote:
> >
> >> Hello,
> >>
> >> For simplicity's sake, let's say I have three fields, A, B and C, all
> >> of which are integers. I'd like the value of C to be equal to A less B
> >> (A-B). Is there a way I can perform this calculation? I'm guessing it
> >> would happen when I INSERT a row and specify the values for A and B.
> >> Feel free to direct me to the fine manual I should have read.
> >>
> >> Thank you.
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=ashish.mukherjee@gmail.co m
> >>
> >>
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=ashish.mukherjee@gmail.co m
>
>

--002215048ac75a717a048e53c4a7--

Re: Performing subtraction between fields

If you want C to always be A-B then it would, in my opinion, be a very
bad idea to store C in the table. Instead you can just put C in your query.

SELECT `A`, `B`, `A` - `B` AS `C` FROM `table`

If that seems like a hassle, you could always create a view using that
select.

Chris W

b@qxhp.com wrote:
> Hello,
>
> For simplicity's sake, let's say I have three fields, A, B and C, all
> of which are integers. I'd like the value of C to be equal to A less B
> (A-B). Is there a way I can perform this calculation? I'm guessing it
> would happen when I INSERT a row and specify the values for A and B.
> Feel free to direct me to the fine manual I should have read.
>
> Thank you.
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performing subtraction between fields

In the last episode (Aug 21), Ashish Mukherjee said:
> Well, since you are searching that column, it's probably a good idea.
>
> Possible scenarios for not storing computed values -
>
> 1) Data integrity - say, the columns A and B are updated but C does not get
> updated, resulting in an anomalous situation

You could use a trigger that updates C whenever A or B changes, to make sure
it stays in synch.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org