CODEWITHSUNDEEP
phpmysqlarraysleft-join
Paige Rose Figueira
Paige Rose Figueira

Reputation: 121

Mysql Left Join for table of sales by month with empty months

I need to make a report with total sales for the month, with a zero value for months with no sales. This is the query I am working with so far, and I feel like I have tried every combination with no luck. I need the months in 1 array and the sales for each month in another array for my report purposes.

query: 

$productSql = "SELECT months.monthnum, SUM(orders.total) FROM months LEFT JOIN orders ON months.monthnum = MONTH(orders.closeddate) WHERE orders.status = 'Closed' GROUP BY MONTH(orders.closeddate) ORDER BY months.monthnum ASC";
$productResult = mysql_query($productSql, $link);

while($productRow = mysql_fetch_array($productResult)){

$label[] = $productRow['months.monthnum'];
$data[] = $productRow['SUM(orders.total)'];
};

echo "[".json_encode($label).",".json_encode($data)."]";

Result: 

[[null,null],["6000.00","3100.00"]]

How do I get it to show all months in the first array, and zero's for the months with no sales in the second array? I think its the GROUP BY that's killing me, but i need that to add up the sales. Help!

PS I know I should discontinue using mysql and I will as soon as this project is finished. Thanks!

EDIT

var_dump results: 

array(1) { [0]=> NULL } array(1) { [0]=> string(7) "6000.00" } array(2) { [0]=> NULL [1]=> NULL } array(2) { [0]=> string(7) "6000.00" [1]=> string(7) "3100.00" }

EDIT 2

SO the problem is in the WHERE clause. I need the where clause because I only want orders that are closed. When I take the WHERE clause out it shows all months with zero for months with no sales and correctly sums the months that do have sales. However it is including ALL orders, not just closed orders. Where do I put the WHERE clause so that I still get all months?

Upvotes: 0

Views: 1458

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can get the zero values using the following query:

SELECT m.monthnum, coalesce(SUM(o.total), 0)
FROM months m LEFT JOIN
     orders o
     ON m.monthnum = MONTH(o.closeddate) AND o.status = 'Closed'
GROUP BY m.monthnum
ORDER BY m.monthnum ASC";

There are three significant changes:

  • The comparison to o.status is moved to the on clause.
  • The aggregation uses the months table rather than the orders table.
  • The total uses coalesce() to get 0 rather than NULL.

I also introduced table aliases, which makes queries easier to write and to read.

Upvotes: 3

Related Questions