CODEWITHSUNDEEP
mysqlstatistics
shin
shin

Reputation: 32721

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?

Upvotes: 2

Views: 12872

Answers (3)

lutz
lutz

Reputation:

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.

Upvotes: 8

Jeff Beck
Jeff Beck

Reputation: 3938

The following should help you get started.

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

Upvotes: 0

Haim Evgi
Haim Evgi

Reputation: 125446

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

Upvotes: 0

Related Questions