How to group by date and make columns from values?

Question

I have a table for orders with two columns: shipping_id that references a shipping method and packed that keeps the date when the order has been packed.

+-------------+------------+
| shipping_id |   packed   |
+-------------+------------+
| 1           | 2017-05-07 |
| 1           | 2017-05-06 |
| 2           | 2017-05-06 |
| 2           | 2017-05-06 |
| 3           | 2017-05-05 |
+-------------+------------+

I need to somehow group the results by the date packed, shipping types put as the columns and values of the cells be the counts of orders that has been packed that day and with that shipping method. Something like this:

+------------+---+---+---+
|    date    | 1 | 2 | 3 |
+------------+---+---+---+
| 2017-05-05 | 0 | 0 | 1 |
| 2017-05-06 | 1 | 2 | 0 |
| 2017-05-07 | 1 | 0 | 0 |
+------------+---+---+---+

Is this possible in MySQL? How would the SQL query look like?

Thank you

There can be an infinite number of distinct shipping_ids every day? — P.Salmon, May 07 '17 at 13:28
I suspect you need mysql conditional aggregation with dynamic sql. — P.Salmon, May 07 '17 at 13:29
@P.Salmon Shippings are stored in separate table to which the ids reference. And yes, theoretically their number isn't limited. — Martin Heralecký, May 07 '17 at 13:34

score 1 · Accepted Answer · edited May 23 '17 at 12:26

You'll not be able to do it with vanilla SQL query, you'll have to use Stored Procedure.

For Dynamic pivot, take a look at MySQL pivot row into dynamic number of columns , but you'll see it's not really easy to use: the GROUP_CONCAT() is limited to 1024 bytes by default and you'll need the right to make and use procedure.

The Vanilla SQL give you Query 1 at best.

SQL Fiddle

Query 1:

select `shipping_id`, `packed`, count(1) as nb
from t
group by `shipping_id`, `packed`
order by `packed`, `shipping_id`

Results:

| shipping_id |     packed | nb |
|-------------|------------|----|
|           3 | 2017-05-05 |  1 |
|           1 | 2017-05-06 |  1 |
|           2 | 2017-05-06 |  2 |
|           1 | 2017-05-07 |  1 |

Query 2:

SET @sql = NULL

Query 3:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(IF(shipping_id = ''',
      shipping_id,
      ''', 1, NULL)) AS `',
      shipping_id,'`'
    )
  ) INTO @sql
FROM t

Query 4:

SET @sql = CONCAT('SELECT packed, ', @sql, ' FROM t GROUP BY packed ORDER BY `packed`')

Query 5:

PREPARE stmt FROM @sql

Query 6:

EXECUTE stmt

Results:

|     packed | 1 | 2 | 3 |
|------------|---|---|---|
| 2017-05-05 | 0 | 0 | 1 |
| 2017-05-06 | 1 | 2 | 0 |
| 2017-05-07 | 1 | 0 | 0 |

Query 7:

DEALLOCATE PREPARE stmt

Dont use `*` in the select when using aggregation. This query may work now but may stop working if new columns are added in the future. State the column names explicitly. — Gurwinder Singh, May 07 '17 at 13:44

How to group by date and make columns from values?

1 Answers1