At the end of every month, we need to know how much we have earned, or how much we have sold every day, in a year for our Zen-Cart-based shop cart. Usually, we have to log in to our admin panel and check the total orders we have sold at a certain time. We have to download the XML files, and add all the orders together; then deleted the unpaid orders and canceled orders. It will take us a lot of time and energy.
Here let me teach you how to use MySQL command to achieve this, only one sentence if only need to know the net sales, say in a month, from “2012-04-01” to “2012-04-30”, we can use the following code:
SELECT date_purchased, SUM(order_total) FROM orders where date_purchased between "2012-04-01" and "2012-04-30" and orders_status <>'1' and orders_status <>'9' ;
Sometimes we may need to know the details, such as unpaid, and canceled orders, then we can use these code:
for total sales, including unpaid and uncanceled orders:
SELECT date_purchased, SUM(order_total) FROM orders where date_purchased between "2012-04-01" and "2012-04-30";
for unpaid orders:
SELECT date_purchased, SUM(order_total) FROM orders where date_purchased between "2012-04-01" and "2012-04-30" and orders_status ='1';
for canceled orders:
SELECT date_purchased, SUM(order_total) FROM orders where date_purchased between "2012-04-01" and "2012-04-30" and orders_status ='9';
To run the above code, we have to log in to MySQL first and run them one by one.