1

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:

enter image description here

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?

1
  • Perhaps you need some JOINs? – Rick James May 7 '20 at 0:55
0

This can be written as a single query. I have guessed the last line.

SELECT
   MONTH(a.Created) AS `Month`
 , SUM(a.totalValue) as totalValue  -- Total amount in $
 , COUNT(a.id) as totalSales
 , SUM(CASE WHEN a.id_delivery = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalDelivery
 , SUM(CASE WHEN a.id_delivery = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
 , SUM(CASE WHEN a.id_payment = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
 , SUM(CASE WHEN a.id_payment = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalCard
 , SUM(CASE WHEN a.id_payment = 3 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalMoney
 , SUM(CASE WHEN b.status IN (7, 8) THEN 1 END) as totalCanceled
FROM order a, orderStatus b
WHERE YEAR(a.Created) = ?
 AND b.id = a.id_status
GROUP BY MONTH(a.Created)
ORDER BY 1
0

This is long, but you mention that you are a beginner. A couple of observations:

  • Don't store numeric attributes as VARCHAR. Does the SUM function even work on a string?
  • Attribute lookup tables are handy, but I recommend using a string as a token rather than a number. The comment in code sample says it all--who is going to know that 3 is OK.
  • Please read up on bind variables
  • How are you going to update the user's age?

You should always use the appropriate data type for each of the columns. Doing data conversion on the fly is a way to have terrible performance and data quality issues.

I use attribute lookup tables and even used your approach years ago--CPU performance and memory was at a premium. That really isn't an issue and people who write queries on your database HATE to do joins on a lookup table. Coding numeric codes is a source of error if they don't do a join. Just use a string token as the key.

You should almost never store an age in a column in a table containing attributes about a person--it's a derived value based on the current date and their birth date. More fundamentally, why do you even need their age? A person cannot place an order if they don't provide their birth date or age? I raise the issue because you should be able to justify why you need every piece of data, particularly security and privacy related attributes. If your application has to undergo an information security audit, you should be able to defend what you are storing and how you are protecting it. Do you want to be responsible if your company/client runs afoul of a statutory requirement (like GDPR) or government/industry regulation? Be mindful of what you are storing in the database!

If you don't use bind variables, the database will have to parse and optimize each query and will likely not reuse caches results, resulting in slow performance.

Aside from that, if you use string concatenation with user input, well let me introduce you to my friend Little Bobby Tables (https://xkcd.com/327/). Your application will not pass a security audit if you do not use bind variables with user input. While this isn't an issue with this code snippet, I wanted to raise that in case you did not know.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.