douglaswalker Posted October 26, 2017 Share Posted October 26, 2017 Hi there I am looking for a way to pull order values for a particular county (New Zealand) between a set of dates. Ultimately I would like to create a report in admin but for now does anyone know or point me toward an SQL statement I could run against the database. many thanks Doug Link to comment Share on other sites More sharing options...
frankl Posted October 26, 2017 Share Posted October 26, 2017 select sum(value) from orders_total ot, orders o where ot.orders_id = o.orders_id and o.delivery_country = 'New Zealand' and ot.class = 'ot_total' and o.date_purchased between '2016-01-01 00:00:00' and '2017-12-31 23:59:59' Depends on what you named 'New Zealand' of course osCommerce user since 2003! Link to comment Share on other sites More sharing options...
douglaswalker Posted October 26, 2017 Author Share Posted October 26, 2017 6 hours ago, frankl said: select sum(value) from orders_total ot, orders o where ot.orders_id = o.orders_id and o.delivery_country = 'New Zealand' and ot.class = 'ot_total' and o.date_purchased between '2016-01-01 00:00:00' and '2017-12-31 23:59:59' Depends on what you named 'New Zealand' of course Hey thanks so much.. Works like a charm. Does ot_total also include postage cost? Link to comment Share on other sites More sharing options...
frankl Posted October 26, 2017 Share Posted October 26, 2017 Yep. If you don't want that then grabbing ot_subtotal would work I think. osCommerce user since 2003! Link to comment Share on other sites More sharing options...
douglaswalker Posted October 27, 2017 Author Share Posted October 27, 2017 Thanks for your help much appreciated Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.