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?

I'm not sure what 'total order' was meant to be just a count of orders? — Jeff Beck
– Jeff Beck, Commented Jan 18, 2010 at 7:48

score 8 · Accepted Answer · 2010-01-18 08:03:04Z

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.

edited Jan 18, 2010 at 8:03

answered Jan 18, 2010 at 7:48

lutz

Sign up to request clarification or add additional context in comments.

2 Comments

shin Over a year ago

Do I need to add sum feild in DB?

Jeff Beck Over a year ago

@shin Nope that is a function in MySQL

Community · Accepted Answer · 2017-05-23 12:19:33Z

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

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Jan 18, 2010 at 7:45

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

Comments

Jeff Beck · Accepted Answer · 2010-01-18 07:46:46Z

0

The following should help you get started.

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

answered Jan 18, 2010 at 7:46

Jeff Beck

3,9383 gold badges31 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to create a query for monthly total in MySQL?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related