PHP-MYSQL : Last 12 months first to last day of each month

Question

我正在进行的一个项目需要获取过去 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 个月内。每个月的平均值。

Answer 1

您可以使用 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)