2

I've this MySQL database table:

+----+------------+---------+
| id |   pdate    | product |
+----+------------+---------+
|  1 | 2015-10-12 | BOX     |
|  2 | 2015-10-12 | SHOE    |
|  3 | 2015-10-12 | PEN     |
|  4 | 2015-10-12 | BOX     |
|  5 | 2015-10-19 | BOX     |
|  6 | 2015-10-12 | SHOE    |
|  7 | 2015-10-19 | SHOE    |
|  8 | 2015-10-19 | PEN     |
|  9 | 2015-10-19 | WATCH   |
| 10 | 2015-10-26 | WATCH   |
| 11 | 2015-10-26 | SHOE    |
+----+------------+---------+

The column product is a dynamic column, I don't know beforehand which products are in this column. And also the pdate column will grow over time and thus I don't know the week numbers beforehand.

I want to get the following output:

+-----------------+-------+-------+
| product | WK 42 | WK 43 | WK 44 |
+-----------------+-------+-------+
|  BOX    |     2 |     1 |     0 |
|  SHOE   |     2 |     1 |     1 |
|  PEN    |     1 |     1 |     0 |
|  WATCH  |     0 |     1 |     1 |
+-----------------+-------+-------+

I want to count the number of records per week per product.

I know I need to do something with MySQL Pivot tables/queries but I'm completely stuck at the moment. How to solve this challenge?

This is not a duplicate question as the other topic is on 2 different tables, this is about a pivot query on a single table. This is a different situation.

C8H10N4O2
  • 18,312
  • 8
  • 98
  • 134
rene
  • 101
  • 7
  • What do you do with this result set afterwards? What if there are no products in week 43? – Strawberry Apr 01 '16 at 11:30
  • If there are no products on WK43 I want to have the value 0 in that cell. – rene Apr 01 '16 at 11:32
  • @rene it was quite simple and I was going to give you the answer, until you replied that you need also "void" weeks on which they aren't any products. This is a bit more complicated as it will require the creation of a "calendar" table. No time to get that long atm. – Thomas G Apr 01 '16 at 11:56
  • @Thomas G If the requirement is not there, so it is an empty cell, what would be the solution than? – rene Apr 01 '16 at 12:17
  • @rene check the duplicate, you'll find the answer for the "simple" sdolution – Thomas G Apr 01 '16 at 12:41

0 Answers0