我所在的项目需要获得过去12个月的平均发票总额。我可以通过做12个查询(每个月1个)很容易地得到我需要的东西,但它非常丑陋,我最终得到了12个唯一的变量(id宁愿有一个循环),我知道有更好的方法。
有人能帮助获得以下最佳方法吗:
SELECT AVG(invoice_total) FROM invoice WHERE date between '2015-01-01' AND '2015-01-31'
SELECT AVG(invoice_total) FROM invoice WHERE date between '2015-02-01' AND '2015-02-28'
SELECT AVG(invoice_total) FROM invoice WHERE date between '2015-03-01' AND '2015-03-31'
等等。。。
我把这些画在一张图上,所以我需要每个月的一个数据点。
最终结果是:
Jan: $2323
Feb: $3523
March: $6453
等等。。。但将在12个月前从当前月份开始。
谢谢。
附言:作为参考,这就是我过去90天的绘图方式:
$get_unconfirmed_appointments = "
SELECT total
, invoice_date
FROM invoice
WHERE clinic_id = 20
AND invoice_date >= '$date'
AND invoice_date <= '$date_two'
ORDER
BY invoice_date ASC
";
$result = $conn->query($get_unconfirmed_appointments) or die($conn->error.__LINE__);
while($row = $result->fetch_assoc()) {
$totals[$row['invoice_date']][] = $row['total'];
}
$sub_arrays = array_chunk($totals, 3);
foreach ($sub_arrays as $threedays) {
foreach ($threedays as $value) {
foreach ($value as $val) {
$all[] = $val;
}
}
$average = array_sum($all) / count($all);
$data_point=round($average,2);
plot($data_point);
}
我需要和上面一样的想法,但在过去的12个月里。每个月的平均值。
您可以使用MONTHNAME函数并按以下方式分组:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_month
您的SQL将是这样的:
SELECT AVG(invoice_total), MONTHNAME(invoice_date) FROM invoice WHERE invoice_date BETWEEN '2014-11-11' AND '2015-02-09' GROUP BY MONTHNAME(invoice_date)