Add together values from JSON by month for a Fusion Charts graph

Question

I am using MySQL, PHP, and Fusion Charts to create a line graph to track the amount of money invoiced for each month in 2016. The two columns in my table that I am using for this graph are InvoiceAmount (Decimal type) and InvoiceDate (DateTime type).

My goal is to have the X-axis of my graph be the dates by month (Jan, Feb, Mar, etc.) from InvoiceDate and the Y-axis be the dollar amount from InvoiceAmount. I got started by converting my MySQL data into a JSON format for it to be readable by Fusion Charts:

//the SQL query to be executed
$query = "SELECT DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, InvoiceAmount FROM Estimates WHERE InvoiceDate IS NOT NULL AND YEAR(InvoiceDate) = 2016 AND InvoiceAmount IS NOT NULL AND InvoiceAmount > 0";

//storing the result of the executed query
$result = $conn->query($query);

//initialize the array to store the processed data
$jsonArray = array();

//check if there is any data returned by the SQL Query
if ($result->num_rows > 0) {
//Converting the results into an associative array
while($row = $result->fetch_assoc()) {
  $jsonArrayItem = array();
  $jsonArrayItem['label'] = $row['InvoiceMonth'];
  $jsonArrayItem['value'] = $row['InvoiceAmount'];
  //append the above created object into the main array.
  array_push($jsonArray, $jsonArrayItem);
  }
}

//Closing the connection to DB
$conn->close();

//set the response content type as JSON
header('Content-type: application/json');
//output the return value of json encode using the echo function.
echo json_encode($jsonArray, JSON_PRETTY_PRINT);

This outputs a JSON like this:

[
{
    "label": "January",
    "value": "11361.00"
},
{
    "label": "December",
    "value": "1164.40"
},
{
    "label": "February",
    "value": "166.80"
},
{
    "label": "July",
    "value": "5088.00"
},
{
    "label": "January",
    "value": "214.50"
},
{
    "label": "June",
    "value": "620.40"
},
{
    "label": "July",
    "value": "5250.00"
},
{
    "label": "March",
    "value": "3425.00"
},
{
    "label": "January",
    "value": "3790.00"
},
{
    "label": "February",
    "value": "1909.80"
},
{
    "label": "January",
    "value": "1780.00"
},
{
    "label": "January",
    "value": "3060.00"
},
{
    "label": "January",
    "value": "2680.00"
},
{
    "label": "February",
    "value": "604.80"
}
]

etc.

As you can see, there are several values that have the same month attached to them. Currently, the line graph is treating each of these label/value pairs as one distinct instance, when what I need is for all of the values from a specific month to be added together to make overall monthly value for Jan, Feb, Mar, etc.

Is there a function I can use in PHP or MySQL that will check which month is attached to the value, and then add all of the values from the same month together? Please let me know the best way to tackle this problem. Thank you for your help.

trincot · Accepted Answer · 2018-04-29T10:11:14.963

5

You could use GROUP BY and SUM to let the SQL statement only return one record per month:

SELECT   DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, 
         SUM(InvoiceAmount) AS InvoiceAmount
FROM     Estimates 
WHERE    YEAR(InvoiceDate) = 2016 
     AND InvoiceAmount > 0
GROUP BY DATE_FORMAT(InvoiceDate, '%M')

Be aware that a query with a hard-coded year is not maintainable in the longer term.

I would suggest showing the last 12 months. The last column gives the "year-month", like 201508. It is used to order the results:

SELECT   DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, 
         SUM(InvoiceAmount) AS InvoiceAmount,
         EXTRACT(YEAR_MONTH FROM InvoiceDate) As InvoiceYearMonth
FROM     Estimates 
WHERE    EXTRACT(YEAR_MONTH FROM InvoiceDate) >= EXTRACT(YEAR_MONTH FROM CURDATE())-100 
     AND InvoiceAmount > 0
GROUP BY DATE_FORMAT(InvoiceDate, '%M')
ORDER BY InvoiceYearMonth

edited Apr 29 '18 at 10:11

answered Aug 19 '16 at 22:07

trincot

317,000
35
244
286

That worked perfectly! Thank you! Does this part of my query: `YEAR(InvoiceDate) = 2016` not work to only grab values from the year 2016? @trincot – Liz Aug 19 '16 at 22:11
1

Yes it does, but are you going to change that every year? Also, when it is early 2017, and you have changed the code to 2017, will it be OK that you only get the 2017 month(s), which might be just January? Showing the latest 12 months might be an interesting alternative, no? – trincot Aug 19 '16 at 22:16
That is very true, and not sustainable in the long term. Do you have a different recommendation? – Liz Aug 19 '16 at 22:17
1

Yes, showing the last 12 months, adding the year to the labels (if you have room for it). – trincot Aug 19 '16 at 22:17
After your excellent recommendation, my final query is: `$query = "SELECT DATE_FORMAT(InvoiceDate, '%M %Y') AS InvoiceMonth, SUM(InvoiceAmount) AS InvoiceAmount FROM Estimates WHERE InvoiceDate >= DATE_SUB(now(), INTERVAL 12 MONTH) AND InvoiceDate IS NOT NULL AND InvoiceAmount IS NOT NULL AND InvoiceAmount > 0 GROUP BY DATE_FORMAT(InvoiceDate, '%M %Y')";` If you have any further recommendations please let me know. – Liz Aug 19 '16 at 22:29
1

I added my recommendation. The problem with your suggestion is that going back 12 months brings you in the middle of some month, so that only part of that month is summed up, giving a wrong impression on your graph. – trincot Aug 19 '16 at 22:33

Add together values from JSON by month for a Fusion Charts graph

1 Answers1