php MySQL：计算具有特定日期的列中所有数字的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13438665/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Calculate sum total of all the figures in a column where has specific date
提问by jQuerybeast
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 月的列价格总和。
回答by eggyal
First change the data type of your datecolumn (remember to update your application code appropriately):
首先更改您的date列的数据类型（记得适当更新您的应用程序代码）：
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'
回答by Mahmoud Gamal
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 DATEinstead.
但是您必须注意日期参数中输入的格式，因为它将作为字符串文字而不是日期对象进行比较。但是，您应该将这些日期存储在数据类型的列中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 MONTHlike so:
要仅选择特定月份的记录，您可以使用 MySQL 函数，MONTH如下所示：
SELECT
SUM(price)
FROM Fuel
WHERE MONTH(`date`) = 9;
