Categories
Development

Get totals of each month a year including the month with no result

I want to echo the total of invoices created per month.

Code below is working so far but I need a 0 if there is no result in a month.

$sql = "SELECT MONTH( invoice_date ) AS MONTH, SUM(`invoice`.`total`) as total
    FROM `invoices`
    WHERE YEAR(invoice_date) = '2019'
    GROUP BY MONTH(invoice_date)";
$result = mysqli_query($con,$sql);
while($data = mysqli_fetch_array($result))
{
    $total = $data['total'];
    $month = $data['month'];
}

What I want is this:

Jan 500
Feb 300
Mar 0
Apr 500
etc...

What I get is this

Jan 500
Feb 300
Apr 500

… because there is no entry with Mar in my DB.

If I do the query in a loop for each month it’s working fine but I know I shouldn’t use it this way and there is a better way!?

Leave a Reply

Your email address will not be published. Required fields are marked *