Magento Statistics System

Total Ordered Report from version 1.4 is generated from a table called: sales_order_aggregated_created

This table is filled up using this query:

SELECT
	DATE(e.created_at) AS 'period',
	e.store_id AS 'store_id',
	e.status AS 'order_status',
	COUNT(e.entity_id) AS 'orders_count',
	SUM(e.total_qty_ordered) AS 'total_qty_ordered',
	SUM(IFNULL(e.base_subtotal_invoiced, 0) * e.base_to_global_rate) + SUM(IFNULL(e.base_discount_refunded, 0) * e.base_to_global_rate) - SUM(IFNULL(e.base_subtotal_refunded, 0) * e.base_to_global_rate) - SUM(IFNULL(e.base_discount_invoiced, 0) * e.base_to_global_rate) - SUM(IFNULL(e.base_total_invoiced_cost, 0) * e.base_to_global_rate) AS 'base_profit_amount',
	SUM(e.base_subtotal * e.base_to_global_rate) AS 'base_subtotal_amount',
	SUM(e.base_tax_amount * e.base_to_global_rate) AS 'base_tax_amount',
	SUM(e.base_shipping_amount * e.base_to_global_rate) AS 'base_shipping_amount',
	SUM(e.base_discount_amount * e.base_to_global_rate) AS 'base_discount_amount',
	SUM(e.base_grand_total * e.base_to_global_rate) AS 'base_grand_total_amount',
	SUM(e.base_total_paid * e.base_to_global_rate) AS 'base_invoiced_amount',
	SUM(e.base_total_refunded * e.base_to_global_rate) AS 'base_refunded_amount',
	SUM(IFNULL(e.subtotal_canceled, 0) * e.base_to_global_rate) AS 'base_canceled_amount'
FROM  `sales_order` as e
WHERE
	e.state NOT IN ('pending_payment', 'new') AND
	e.created_at LIKE '2010-10-22%';

NOTES:
- Profit is zero if you don’t generate invoices.

Tags: profit, sales_order_aggregated_created, zero