MySQL: Calculate sum total of all the figures in a column where has specific date

mysql, php, sum

Solution

First change the data type of your `date` column (remember to update your application code appropriately):

ALTER TABLE Fuel ADD newdate DATE;
UPDATE Fuel SET newdate = STR_TO_DATE(date, '%d-%m-%Y');
ALTER TABLE Fuel DROP date, CHANGE newdate date DATE FIRST;

Then you can:

SELECT SUM(price) FROM Fuel WHERE date BETWEEN '2012-09-01' AND '2012-09-30'

Problem

How can I get the sum of the column price for a specific month. The date column is a varchar(10) and the date format is European ( dd-mm-yy ). Here is a sample of my table: Currently to select all sum of price I use: ``` case 'get': $q=mysql_real_escape_string($_GET['q']); $query="SELECT sum(price) FROM Fuel"; $result = mysql_query($query); $json = array(); while($row = mysql_fetch_array($result)) { $json['price']=$row['price']; } print json_encode($json); mysql_close(); break; ``` So how can I get the sum of column price for month 09-2012.

Original source