Mysql – SQl query to create a report based on multiple relational tables

I have an e-commerce website and I'm in the process of creating a sales report. These reports need to be divided by year and month.

Each sale made on the website will generate a relational link between multiple tables, where I need to get some values to display the proper report. Besides showing just what items were sold, I also need to show, for example, how many of those sales were made using credit card, money, etc… How many of them were shipped via postal office, third part services, etc.

Before I continue, keep in mind I'm not an advanced user in dataBases/BackEnd, so any improvement/suggestions is very welcome.

I'm using PHP/SQL on the backend and a MySql database with a model as the following image:

The basic information I need to know is (all of these are the total for each month):

• Value: The total amount of sales;
• Sales: Total sales (based on orderStatus);
• Payment: Total sales using each payment type;
• Delivery: Total sales using each delivery type;
• Reported sales: Total sales reported by user (based on orderStatus);
• Canceled sales: Total sales canceled (based on orderStatus);

I was able to built this report, but It seems to be very poor and/or not the right way to do this. It felt very messy, so this is why I'm here to ask your help to guide me on how to build this type of query and to improve my code.

To create the segment by Year/Month I'm using this code:

SELECT DISTINCT YEAR(created) as 'year' FROM order
for ($i=1; $i<=12; $i++) { ... }

And this is the code I'm running inside the for to get the sales for each month:

//Range between 3-6 are status of OK (shipped, delivered, taken)
$query = "
    FROM order a, orderStatus b
    WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' 
    AND b.id = a.id_status AND b.status >= 3 AND b.status <= 6
";

$totalAmount = sql("SELECT SUM(a.totalValue) as 'totalValue' $query"); //Total amount in $
$totalSales  = sql("SELECT COUNT(a.id) as 'totalSales' $query");

//Total by delivery type
$totalDelivery = sql("SELECT COUNT(a.id_delivery) as 'totalDelivery' $query AND a.id_delivery = 1");
$totalTaken    = sql("SELECT COUNT(a.id_delivery) as 'totalTaken' $query AND a.id_delivery = 2");

//Total by payment type
$totalOnline = sql("SELECT COUNT(a.id_payment) as 'totalOnline' $query AND a.id_payment = 1");
$totalCard   = sql("SELECT COUNT(a.id_payment) as 'totalCard' $query AND a.id_payment = 2");
$totalMoney  = sql("SELECT COUNT(a.id_payment) as 'totalMoney' $query AND a.id_payment = 3");

//Total canceled/Reported sales
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 7");
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 8");

As you can see, the sql works, but it's not very optimal. Also, It feels like I'm repeating myself a lot.

For example, I created a variable with the most common piece of code used in each query, most of them need to match an specific range of orderStatus, and I need to get the total count of those informations. With the exception of the canceled and reported sales, which are of a different orderStatus. Besides that, i'm creating a manual check on the delivery and payment status, it could be somehow automated.

The way I'm doing will also return every type of result, for example, if there were no payment with money, it will execute the query and return 0. This isn't needed, as I can check for a valid and existing information on the FrontEnd. If this can be done, great, but if not, it's not a big problem to get only when there is data stored.

So, how can I optimize this code, or even my database structure in order to have a better result/query to generate these sales report?