2

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?

rae1
  • 6,066
  • 4
  • 27
  • 48
shin
  • 31,901
  • 69
  • 184
  • 271

3 Answers3

8

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.

0

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 :

MySQL/SQL: Group by date only on a Datetime column

Community
  • 1
  • 1
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
0

The following should help you get started.

select sum(total) from omc_order group by month(order_date), year(order_date)
Jeff Beck
  • 3,944
  • 3
  • 28
  • 45