Magento SQL Average Total Orders Per Customer

First we need to get the total number of orders:

$result = mysqli_query($con,”

SELECT count(mg_sales_flat_order.status) as grand_count
FROM mg_sales_flat_order
WHERE mg_sales_flat_order.status NOT LIKE ‘canceled’
AND mg_sales_flat_order.status NOT LIKE ‘closed’
AND mg_sales_flat_order.status NOT LIKE ‘fraud’
AND mg_sales_flat_order.status NOT LIKE ‘holded’
AND mg_sales_flat_order.status NOT LIKE ‘paypal_canceled_reversal’

$totalorders1 = $row[‘grand_count’];

“);

while($row = mysqli_fetch_array($result)) {

$totalorders = $row[‘grand_count’];

echo $totalorders;
}

Then we need to get the total number of customers:

$result = mysqli_query($con,”

SELECT COUNT(*) AS grand_count FROM(
SELECT customer_email FROM mg_sales_flat_order
WHERE mg_sales_flat_order.status NOT LIKE ‘canceled’
AND mg_sales_flat_order.status NOT LIKE ‘closed’
AND mg_sales_flat_order.status NOT LIKE ‘fraud’
AND mg_sales_flat_order.status NOT LIKE ‘holded’
AND mg_sales_flat_order.status NOT LIKE ‘paypal_canceled_reversal’
GROUP BY customer_email
) s”);

while($row = mysqli_fetch_array($result)) {

$totalcustomers = $row[‘grand_count’];

echo $totalcustomers1;

}

We then need to do total orders divided by total customers to give us the average number of orders per customer

$decimal_places = 2;

echo number_format($totalorders/$totalcustomers, $decimal_places);

Magento | | No Comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

« »