Help grouping / counting sales data by date

Hi guys,

Not sure How I begin to do this…

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.

Any help would be greatly appreciated :slight_smile:

OMG!!! An here I was going to attempt to write some intricated php code and sql query to try and achive what you didi with a little SQL!!

Thank you sooooooo much!!!

PHPmyadmin gave me this to use for my php coding…
How would I echo out the results in a table?

Date | amt of orders | total in sales


<?

$sql = "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";

?>

Is it possible to format the date that echoes to dd-mm-yyyy?

Lastly how would I change the query above to show for last month and even the month before that?

hmmm ok, thanks all the same… Much appreciated :slight_smile:

no idea, sorry, i do coldfusion, not php

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

:slight_smile: