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

Question

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.

Answer 1

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'

Answer 2

This will work for you:

SELECT 
  sum(price) 
FROM Fuel
WHERE datefield = 'somedate';

But you have to watch out the format entered in the date parameter, because it will be compared as a string literal not as a date object. However, you should store these dates in a column of data type DATE instead.

Update:

How can I select sum for all months with 09?

To select only records for a specific month, you can use the MySQL function MONTH like so:

SELECT 
  SUM(price) 
FROM Fuel
WHERE MONTH(`date`) = 9;

SQL Fiddle Demo