We would like to publish some useful SQL queries from time to time that may help you gather information from your shop. The queries are written to copy and paste them directly to phpMyAdmin or similar database clients. If you want to use them in your code, please replace the prefix “jos_vm_” with “#__{vm}_” to make it work with the database class of virtuemart. Also, if you have another prefix chosen for your tables, replace the string with your prefix.
Sum up monthly order totals
This query selects the order_total from the orders table and displays monthly sums. The query selects only orders with the order-status “C”.
SELECT sum(order_total) as totalamount, year(from_unixtime(mdate)) as totalyear, month(from_unixtime(mdate)) as totalmonth, monthname(from_unixtime(mdate)) as totalstrmonth FROM `jos_vm_orders` WHERE `order_status`="C" group by totalyear, totalmonth order by totalyear asc, totalmonth asc
VM-Expert.com is part of the B01 Consulting Network. Since 2003 we build shops and websites with Joomla and Virtuemart. We are specialised in developing custom Joomla and Virtuemart Extensions.
TrackBack URI for this entry
