每月总数返回零，并且只输出运行总数

I am using the following to try and output my monthly totals in a format such as :

我正在使用以下格式尝试并输出我的每月总数，例如:

 January
 Quoted Total : £678
 Parts Total : £432
 Profit Total : £244

 February
 Quoted Total : £650
 Parts Total : £345
 Profit Total : £123

etc..........

等..........

    // Work Complete Totals
    $query = $db->query("SELECT SUM(pricequoted) AS pricequotedtotal,
                                SUM(partprice) AS partpricetotal,
                                SUM(profit) profittotal,
                                DATE_FORMAT('%Y-%m', completeddate) AS month 
                         FROM `jobdetails` 
                         WHERE jobstatus='complete'
                         GROUP BY DATE_FORMAT('%Y-%m', completeddate)");


    echo '<div style="float:right; padding-right:10px;">';
    echo '<strong>Work Complete Totals</strong>';

    while($result = $query->fetch_object()) {

        $pricequoted = number_format($result->pricequotedtotal, 2, '.', '');
        $partprice   = number_format($result->partpricetotal, 2, '.', '');
        $profit      = number_format($result->profittotal, 2, '.', '');

        echo '<p><strong style="color:red;">Quoted Total : &pound;'.$pricequoted.'</strong></p>';
        echo '<p><strong style="color:Darkorange ;">Parts Total : &pound;'.$partprice.'</strong></p>';
        echo '<p><strong style="color:green;">Profit Total : &pound;'.$profit.'</strong></p>';
    }

    echo '</div>';

The problem I am getting is that it is only outputting the running totals, so I get the following at the bottom of the page ONCE,but nothing more :

我遇到的问题是，它只输出运行总数，所以我在页面底部得到如下内容，但仅此而已:

Work Complete Totals
Quoted Total : £1460.00

Parts Total : £541.43

Profit Total : £918.57

If I run the above query in phpmyadmin I get the result :

如果我在phpmyadmin中运行上面的查询，我得到的结果是:

pricequotedtotal    partpricetotal  profittotal        month
      1460              541.43      918.5699999999998   NULL

The layout of my table is as follows :

我的表格的布局如下:

id  customerID  name    facebookuserurl tel email   address itemforrepair   repairdetails   otherdetails    pricequoted partprice   profit  datepartordered jobstatus   dateofcompletion    datecreated itemnumber

Below is a sample row :

下面是一个示例行:

49  37ac4   Ellen Frost https://www.facebook.com/ellen.mccormick.18             Galaxy S3 (Fullsize) Blue   Broken front glass and also digitiser not working.  Quoted customer on whole lcd, digitiser assembly r...   140 114.98  25.02   2013-05-02  complete    2013-05-08  2013-05-01  251258104217

EDIT >> Below is a screen shot of several rows in the table. 每月总数返回零，并且只输出运行总数

编辑下面的>>是表中几行的屏幕快照。

EDIT >>

编辑> >

This is my table structure : 每月总数返回零，并且只输出运行总数

这是我的表格结构:

4 个解决方案

#1

You need to change this

你需要改变这个

DATE_FORMAT('%Y-%m', completeddate)

来

DATE_FORMAT(completeddate, '%Y-%m')

That's why you get NULL in your month column and therefore just one row.

这就是为什么你的月列是空的，因此只有一行。

See manual entry.

看到人工输入。

#2

Ian,

伊恩,

I guess the following query will help you.

我想下面的查询会对您有所帮助。

SELECT SUM(pricequoted) AS pricequotedtotal, SUM(partprice) AS partpricetotal, SUM(profit) profittotal, MONTHNAME(dateofcompletion) FROM jobdetails WHERE jobstatus='complete' GROUP BY DATE_FORMAT(dateofcompletion, '%Y-%m') order by dateofcompletion;

选择SUM(price)作为pricetedtotal, SUM(partprice)作为partpricetotal, SUM(profit) total, monthly name (dateofcompletion) FROM jobdetails (jobdetails) WHERE jobstatus='complete' complete' GROUP BY DATE_FORMAT(dateofcompletion， '%Y-%m') order BY dateofcompletion BY dateofcompletion;


    +------------------+----------------+-------------+-----------------------------+
    | pricequotedtotal | partpricetotal | profittotal | MONTHNAME(dateofcompletion) |
    +------------------+----------------+-------------+-----------------------------+
    |              140 |         114.98 |       25.02 | May                         | 
    |              140 |         114.98 |       25.02 | June                        | 
    +------------------+----------------+-------------+-----------------------------+
2 rows in set (0.00 sec)

#3

SUM(pricequoted) AS pricequotedtotal,
SUM(partprice) AS partpricetotal,
SUM(profit) profittotal,

Should be

应该是

SUM(pricequoted) AS pricequotedtotal,
SUM(partprice) AS partpricetotal,
SUM(profit) AS profittotal,

You missed out an "AS"

你错过了a

#4

Well you may try this query

你可以试试这个查询

SELECT SUM(pricequoted) AS pricequotedtotal,
       SUM(partprice) AS partpricetotal,
       SUM(profit) AS profittotal,
       MONTHNAME(completeddate) AS month 
           FROM `jobdetails` 
       WHERE jobstatus='complete'
           GROUP BY MONTH(completeddate)

Column 'completeddate' doesn't exist in the table above, instead use 'dateofcompletion'

表中不存在“completeddate”列，而是使用“dateofcompletion”

So, running the query and putting them in while loop will produce exactly what u wanted

因此，运行查询并将它们放入while循环将生成您想要的结果

Also I saw your table structure, it's better to format a table a bit using varchar, int, float and so on .. text columns consume more memory

我还看到了您的表结构，最好使用varchar、int、float等对表进行一点格式化。文本列消耗更多的内存

http://www.pythian.com/blog/text-vs-varchar/

http://nicj.net/mysql-text-vs-varchar-performance/

#1