如何获得每月的总销售额

我试图在一个名为Flot&amp; amp;的jquery栏上展示每个月的销售情况。 这是我的查询</ p>

  $ q = $ db-&gt; getRows(“SELECT date AS dates,
COCOUNT(id)AS sales FROM orders
WHERE date BETWEEN?AND?

GROUP BYdate ORDER BYdate“,
PDO :: FETCH_ASSOC,[”$ year-01-01“,”$ year-12-31“]);
</ code> </ pre>

以下是我如何利用我的查询</ p>

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

foreach( $ q as $ Viewed){
$ the_day =(int)substr($ seen ['dates'], - 2); //从日期开始一天

$ views [$ the_day] = $ Viewed ['sales'];
}
返回$ views;
</ code> </ pre>

这是我的代码的响应</ p>

  Array 

[1] =&gt; 0
[2] =&gt; 0
[3 ] =&gt; 0
[4] =&gt; 0
[5] =&gt; 0
[6] =&gt; 0
[7] =&gt; 0
[8] =&gt; 0

[9] =&gt; 0
[10] =&gt; 0
[11] =&gt; 0
[12] =&gt; 0
[0] =&gt; 1

</ code> </ pre>

它应该显示最后的[0] =&gt; 1个值进入第9个月。
我在这个查询中出错了吗?</ p>
</ div>

展开原文

原文

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?

dongxing9219
dongxing9219 您可以在GROUPBY子句中使用extract(MONTHFROMdate)。即使您使用where子句来限制一个特定年份,我也会将提取(YEARFROMdate)添加到groupby子句中,以防您需要列表数年。另请参见:modern-sql.com/feature/extract
大约 3 年之前 回复
doubi6898
doubi6898 基于主题行,我原本希望在某处看到sum()聚合。
大约 3 年之前 回复
dragon5006
dragon5006 你自己在代码中的评论说明了这是错误的
大约 3 年之前 回复
dongwuwan5646
dongwuwan5646 尝试GROUPBY月(日期)和选择使用月(日期)。然后只需获取结果和输出。
大约 3 年之前 回复

1个回答



您可以直接从数据库中获取值,例如:</ p>

 “SELECT \  n月(日期)AS月
,COUNT(id)AS sales
FROM orders
WHERE date BETWEEN?AND?
GROUP BY month(date
ORDER BY month(date)“\ n </ code> </ pre>
</ div>

展开原文

原文

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`)"

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐