How to create a query for monthly total in MySQL?

Question

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.

Answer 1

you need to Cast the datetime to a mount , then group by

SELECT SUM(foo), MONTH(mydate) DateOnly FROM a_table GROUP BY DateOnly; 

see a close question :

http://stackoverflow.com/questions/366603/mysql-sql-group-by-date-only-on-a-datetime-column

Answer 2

The following should help you get started.

select sum(total) from omc_order group by month(order_date), year(order_date)

Answer 3

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.