如何获得每月的总销售额

Question

I'm trying to show sale of every month on a jquery bar called Flot & here is my query

$q = $db->getRows("SELECT date AS dates,
COUNT(id) AS sales FROM orders 
WHERE date BETWEEN ? AND ? 
GROUP BY `date` ORDER BY `date`", 
PDO::FETCH_ASSOC, ["$year-01-01", "$year-12-31"]);

Here is how I'm utilizing my query

$views = array_fill(1, (int) 12, 0);

foreach ($q as $viewed) {
        $the_day = (int) substr($viewed['dates'], -2);  // get the day from the date

        $views[$the_day] = $viewed['sales'];
}
 return $views;

This is the response of my code

Array
(
    [1] => 0
    [2] => 0
    [3] => 0
    [4] => 0
    [5] => 0
    [6] => 0
    [7] => 0
    [8] => 0
    [9] => 0
    [10] => 0
    [11] => 0
    [12] => 0
    [0] => 1
)

It should show the last [0] => 1 value into the 9th month. Where I'm wrong in this query?

Answer 1

You could obtain the values directly from the db eg:

  "SELECT 
      month(date) AS month
      , COUNT(id) AS sales 
      FROM orders     
  WHERE date BETWEEN ? AND ? 
  GROUP BY month(`date`) 
  ORDER BY month(`date`)"