MySQL：计算具有特定日期的列中所有数字的总和

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 ).

日期列是varchar(10),日期格式是欧洲(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.

那么如何才能获得09-2012月的柱价总和。

2 个解决方案

#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'

#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.

但是你必须注意在date参数中输入的格式,因为它将作为字符串文字而不是日期对象进行比较。但是,您应该将这些日期存储在DATE数据类型的列中。

Update:

How can I select sum for all months with 09?

如何用09选择所有月份的总和?

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

要仅选择特定月份的记录,可以使用MySQL函数MONTH,如下所示:

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

SQL Fiddle Demo