[SOLVED] Query to export total sale

[DARKF3D3]

I need a query that given a date range allow to export total amount (all orders with invoices) grouped by day.

 

Something like this:

(date range 01/01/2014-06/01/2014)

01/01/2014 2551€

02/01/2014 1930€

03/01/2014 995€

04/01/2014 1280€

05/01/2014 3203€

06/01/2014 1550€

 

 

Someone can help me with this query?

Edited January 25, 2015 by DARKF3D3 (see edit history)

[tuk66]

Look at Stats > Stats Dashboard (1.6). It shows daily revenue.

[DARKF3D3]

Thanks tuk66,

I saw that page but i need to have a query that i can run directly onto the database.

Maybe the query used by that page can be taken somewhere in PS files?

[tuk66]

$data_table['totalSales'] in hookAdminStatsModules method in /modules/statsforecast/statsforecast.php

[DARKF3D3]

Looking at that file i copied (and edited a little bit) this query but I can't let it works:

SELECT
'.$date_from_ginvoice.' as fix_date,
COUNT(*) as countOrders,
SUM((SELECT SUM(od.product_quantity) FROM '._DB_PREFIX_.'order_detail od WHERE o.id_order = od.id_order)) as countProducts,
SUM(o.total_paid_tax_excl / o.conversion_rate) as totalSales
FROM '._DB_PREFIX_.'orders o
WHERE o.valid = 1
AND o.invoice_date BETWEEN '2014/12/01' and '2014/12/31'
GROUP BY o.invoice_date

[tuk66]

Thiy query works for me

SELECT
'11111' as fix_date,
DATE(o.invoice_date),
COUNT(*) as countOrders,
SUM((SELECT SUM(od.product_quantity) FROM ps_order_detail od WHERE o.id_order = od.id_order)) as countProducts,
SUM(o.total_paid_tax_excl / o.conversion_rate) as totalSales
FROM ps_orders o
WHERE o.valid = 1
AND o.invoice_date BETWEEN '2014/01/01' and '2014/12/31'
GROUP BY DATE(o.invoice_date)