How to create a query for monthly total in MySQL?

I have the following DB.

CREATE TABLE IF NOT EXISTS `omc_order` (
  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `order_date` datetime NOT NULL,
  `delivery_date` datetime NOT NULL,
  `payment_date` datetime NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

I want to create a statistic page to see total payment and total order monthly.

In one page I want to display something like this.

Month    Year    Total Order    Total Payment
Sep       09       800             760
Oct       09       670             876
Nov
...
...

Now I am not sure how to create query for this.

Could anyone give me some ideas how to approach this please?

Thanks in advance.

This question and answers originated from www.stackoverflow.com
Question by (1/18/2010 7:37:58 AM)

Answer

You can use the Mysql date and time functions to group the rows for each month and them sum them up. Something like this could be a starting point:

  SELECT monthname(payment_date) AS Month,
         year(payment_date) AS Year,
         sum(total) AS 'Total Order',
         sum(??) AS 'Total Payment'
    FROM omc_order
ORDER BY payment_date
GROUP BY month(payment_date),
         year(payment_date);

I'm not sure how you compute the values for Total Payment.

Edit: sum() is a MySQL function.

Answer by
Find More Answers
Related Topics  mysql  statistics
Related Questions