MySQL Sum and Subtract according to the records?

I have a record in mysql which contains something like this:

+15,+23,-21,+23,-3 

All I want to do is that in a SQL query these values get added and subtracted according to their signs.

I know of SUM(), but what about the subtraction? Basically, I ne开发者_Go百科ed something that can be executed in one query, like the following example:

SELECT SUM(result) AS 'total' FROM mytable  WHERE year='2011' AND month='10';

Your query will work correctly. Tested on

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `p` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into test values(null, 5), (null, -1), (null,4);
select sum(p) from test;

Result is 8.

You are using a comma separated field in your database.

That's a huge NONO, (My)SQL is not very well equipped to deal with this.
If you have no more than 5 values in every row you can do something like:

Awkward code warning

$month = mysql_real_escape_string($data['month']);
$year = mysql_real_escape_string($data['year']);
$sql = "SELECT 
  IFNULL(SUBSTRING_INDEX(result,',',1),0) 
  + IFNULL(SUBSTRING_INDEX(result,',',2),0)
  + IFNULL(SUBSTRING_INDEX(result,',',3),0)
  + IFNULL(SUBSTRING_INDEX(result,',',4),0)  
  + IFNULL(SUBSTRING_INDEX(result,',',5),0) as total_result
FROM mytable
WHERE `year` = '$year' AND `month` = '$month' ";

I'm not 100% sure MySQL will be happy with the '+' signs, if not you'll need to filter them out by doing REPLACE(IFNULL(SUBSTRING_INDEX(result,',',2),0),'+','')

See:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Refactoring you database
I recommend that you kick the CSV habit and normalize your database.

Put the values into different rows (one value per row) and merge the month and year columns into one, and add a auto_increment integer primary key column named id

Your table will look something like:

id    result    mydate
1     15       2011-01-01
2     13       2011-01-01
3     -15      2011-01-01
...
4875  -23      2011-08-28

Now you can add the sum of results in a particular month by doing:

$sql = "SELECT sum(result) as totalresult 
FROM mytable
WHERE mydate BETWEEN '$startdate' AND '$enddate' ";

Using the between ... and construct instead of the functions YEAR(mydate) and MONTH(mydate) will allow MySQL to use an index to select the values.
You you wrap a column in a function MySQL cannot use an index slowing things down a lot.