I run an online store and wish to display a report for ‘daily’ sales data similiar to below:
[B]1st june - 6 orders - $520.50 in sales
2nd june - 4 orders - $356.00 in sales
etc…
next month - prev month[/B]
My order total for each individual order is being stored in a table called ‘Customers’ and the ordered products is being stored in a table called ‘phpcart_items_ordered’
CREATE TABLE IF NOT EXISTS `Customers` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`order_id` varchar(50) NOT NULL default '',
`order_total` decimal(10,2) NOT NULL default '0.00',
`date_ordered` datetime NOT NULL default '0000-00-00 00:00:00',
----plus other non-relevant data
CREATE TABLE IF NOT EXISTS `phpcart_items_ordered` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`order_id` varchar(50) NOT NULL default '',
`product_id` varchar(50) NOT NULL default '',
`product_name` varchar(100) NOT NULL,
`date_ordered` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1837 ;
I currently have coding that shows monthly stats but dont know how to break this down into daily stats.
yes, using the DATE_FORMAT function, although best practice says that you should do date formatting and other localizations in the front end, not in the sql
more cowbells and more INTERVAL arithmetic in the WHERE clause
SELECT DATE(date_ordered) AS thedate
, COUNT(*) AS orders
, SUM(order_total) AS sales
FROM Customers
WHERE date_ordered >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
AND date_ordered < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
+ INTERVAL 1 MONTH
GROUP
BY thedate