calculate the Change

[th1982]

How can i caculate the Change of current Price and yesterday Price,it's 2 rows of colume Price in same table.and the result "Change" is another colume beside Price colume.it's very easy in Excel but i dont know how to do in mysql
Thanks
Best Regards

[ronverdonk]

From your post I cannot deduct what your table/rows look like.
Show a better description of the table rows involved.

Ronald

[th1982]

We have 1 table with 2 columes Price and Change of Share
for ex: yesterday Share Price was 20
today Share Price is 30 ,so Change is 10
the point is how we calculate Change by using mysql syntax
Thanks so much

[ronverdonk]

If you don't want to show the columns of the row involved, fine with me.

I cannot image that your row only has 2 columns! What about a date, a share number or name, etc.?

When you want us to help you by not giving information, then not. But then don't ask for help.

Ronald

[th1982]

sorry man,but i really dont have this table.now i create the table and some data for you :
[code=mysql]
CREATE TABLE `vf1` (
`Date` date NOT NULL default '0000-00-00',
`Price` varchar(255) NOT NULL default '',
`Change` varchar(65) NOT NULL default '',
`Vol` varchar(255) NOT NULL default '',
`NAV` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `vf1`
--

INSERT INTO `vf1` VALUES('2008-02-12', '31', '2.3', '18013', '42.134');
INSERT INTO `vf1` VALUES('2008-02-12', '20', '-2.3', '18013', '41.458');
INSERT INTO `vf1` VALUES('2008-02-12', '25.2', '1.3', '18013', '40.000');
INSERT INTO `vf1` VALUES('2008-02-12', '25.2', '0.0', '18013', '39.431');
INSERT INTO `vf1` VALUES('2008-02-14', '24.8', '0.3', '12760', '38.997');
INSERT INTO `vf1` VALUES('2008-02-15', '24.3', '0.1', '7720', '39.209');
INSERT INTO `vf1` VALUES('2008-02-18', '24.3', '-0.5', '7720', '39.380');
INSERT INTO `vf1` VALUES('2008-02-20', '23', '-0.1', '39861', '38.498');
INSERT INTO `vf1` VALUES('2008-02-21', '20.9', '-0.1', '31515', '38.520');
INSERT INTO `vf1` VALUES('2008-02-22', '20.8', '-0.1', '32923', '38.371');
INSERT INTO `vf1` VALUES('2008-02-25', '21.8', '1.0', '20409', '38.150');
INSERT INTO `vf1` VALUES('2008-02-26', '20.8', '-1.0', '66111', '37.569');
INSERT INTO `vf1` VALUES('2008-02-27', '20.8', '0.0', '14011', '36.375');
INSERT INTO `vf1` VALUES('2008-03-04', '18.1', '-0.9', '7706', '35.718');
INSERT INTO `vf1` VALUES('2008-03-10', '19.40', '-0.5', '460020', NULL);
INSERT INTO `vf1` VALUES('2008-03-11', '18.5', '-0.9', '339040', NULL);
INSERT INTO `vf1` VALUES('2008-03-12', '18.6', '0.1', '432420', NULL);
[/code]Change colume ,i need to type in manually,now i want it auto caculating when i have Price values.
thanks man.

Please enclose any code within the proper code tags. See the Posting Guidelines on how to do that.

MODERATOR

[ronverdonk]

I cannot see how to do this in 1 query. But I will show you an example where you can do it in one query, but it requires an numeric column (like an auto increment value) with exact sequence.
It has this table

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| date  | date    | YES  |     | NULL    |                |
| price | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

and this content

+----+------------+-------+
| id | date       | price |
+----+------------+-------+
|  1 | 2008-10-10 |    30 |
|  2 | 2008-10-12 |    32 |
|  3 | 2008-10-13 |    28 |
|  4 | 2008-10-14 |    38 |
|  5 | 2008-10-15 |    40 |
+----+------------+-------+

and this query[code=mysql]select t1.date as date1, t2.date as date2, t2.price-t1.price as difference from prices as t1 inner join prices as t2 on t1.id+1 = t2.id order
by t1.id;[/code]which results in

+------------+------------+------------+
| date1      | date2      | difference |
+------------+------------+------------+
| 2008-10-10 | 2008-10-12 |          2 |
| 2008-10-12 | 2008-10-13 |         -4 |
| 2008-10-13 | 2008-10-14 |         10 |
| 2008-10-14 | 2008-10-15 |          2 |
+------------+------------+------------+

See if you can use this, assuming you can add a num sequence column.

Ronald

[th1982]

thanks for yr help
i ve learn from yr example
see yr help next time
thanks

[ronverdonk]

You are welcome. See you here next time.

Ronald