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. enter image description here

EDIT >>

This is my table structure : enter image description here

What does to do mysqli with this question? It is mere API to run your query. I'd rather tag it with SQL than mysqli. — Your Common Sense, May 29 '13 at 08:48

score 6 · Accepted Answer · answered May 29 '13 at 08:51

6

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.

answered May 29 '13 at 08:51

fancyPants

50,732
33
89
96

When I change that I get no total at all. – Iain Simpson May 29 '13 at 09:26
How can I add the month name or number to this so that I know what month each result is from ?. – Iain Simpson May 29 '13 at 09:44
You can use the MONTHNAME() function. http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_monthname `SELECT MONTHNAME(completeddate) ...GROUP BY DATE_FORMAT(completeddate, '%Y-%m')...` – fancyPants May 29 '13 at 09:53

score 5 · Answer 2 · answered Jun 05 '13 at 05:01

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)

SDZ · Answer 3 · 2013-05-24T09:07:52.220

4

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"

edited May 24 '13 at 09:07

answered May 24 '13 at 08:38

SDZ

726
2
8
21

That doesnt display anything – Iain Simpson May 24 '13 at 08:45
All I get with that is the header "Work Complete Totals" and nothing under it. – Iain Simpson May 24 '13 at 08:46
Replace it with yours. You missed something out. – SDZ May 24 '13 at 09:06
I did replace it with mine and get the above result – Iain Simpson May 24 '13 at 09:16
Adding the AS just gives me the rolling total again (sum off ALL of the records that are completed) – Iain Simpson May 24 '13 at 09:18

Besmir Sadiku · Answer 4 · 2013-06-03T12:27:06.507

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/

Monthly totals month returning NULL, and only outputting running total

4 Answers4