CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 4728

Trying to see how many orders by year/month for customers based on itemised list of orders

I have a simple database table listing customerID and orderDate. I would like to produce a grid showing how many orders each customer has placed month between a date range

e.g.

customerID  orderDate
1           2015-01-15
1           2015-01-25
1           2015-02-01
1           2015-04-05
2           2015-02-15
2           2015-02-25
2           2015-03-01
2           2015-03-05
3           2015-02-01
3           2015-05-08

And then what I am looking for is:

customerID  jan2015 feb2015 mar2015 apr2015 may2015
1           2       1       0       1       0
2           0       2       2       0       0
3           0       1       0       0       1

Upvotes: 1

Views: 108

Answers (1)

AdamMc331
AdamMc331

Reputation: 16690

You can use conditional aggregation for this. MySQL has several helpful date and time functions which you can use for queries like this. In this example, I would take note of the YEAR() and MONTH() functions. Then you can write conditional statements for each column the count the rows that occur in each month and year, like this:

SELECT customer, 
  SUM(MONTH(orderDate) = 1 AND YEAR(orderDate) = 2015) AS jan2015,
  SUM(MONTH(orderDate) = 2 AND YEAR(orderDate) = 2015) AS feb2015,
  SUM(MONTH(orderDate) = 3 AND YEAR(orderDate) = 2015) AS mar2015,
  SUM(MONTH(orderDate) = 4 AND YEAR(orderDate) = 2015) AS apr2015,
  SUM(MONTH(orderDate) = 5 AND YEAR(orderDate) = 2015) AS may2015
FROM orders
GROUP BY customer;

Here is an SQL Fiddle example.

Upvotes: 1

Related Questions