Monthly totals month returning NULL, and only outputting running total

Question

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 :

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 :

Answer 1

You need to change this

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

to

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

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

See manual entry.

Answer 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;

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

Answer 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"

Answer 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'

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

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

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

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