Let me start with what I'm trying to get - here's an example for the output
So for this, I've got a table and some data
CREATE TABLE orders_test(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
order_number INT,
customer_number INT,
customer_name VARCHAR(90),
order_date DATE,
order_amount DECIMAL(13,2),
PRIMARY KEY (`id`)
);
INSERT INTO orders_test (id, order_number, customer_number, customer_name, order_date, order_amount) VALUES
(1, 100, 5001, 'Wayne Enterprises', '2018-11-14', 100.00),
(2, 101, 6002, 'Star Labs', '2018-11-15', 200.00),
(3, 102, 7003, 'Daily Planet', '2018-11-15', 150.00),
(4, 103, 5001, 'Wayne Enterprises', '2018-11-21', 110.00),
(5, 104, 6002, 'Star Labs', '2018-11-22', 175.00),
(6, 105, 6002, 'Star Labs', '2018-11-23', 117.00),
(7, 106, 7003, 'Daily Planet', '2018-11-24', 255.00),
(8, 107, 5001, 'Wayne Enterprises', '2018-12-07', 321.00),
(9, 108, 6002, 'Star Labs', '2018-12-14', 55.00),
(10, 109, 7003, 'Daily Planet', '2018-12-15', 127.00),
(11, 110, 6002, 'Star Labs', '2018-12-15', 133.00),
(12, 111, 5001, 'Wayne Enterprises', '2018-12-17', 145.00),
(13, 112, 7003, 'Daily Planet', '2018-12-21', 111.00),
(14, 113, 6002, 'Star Labs', '2018-12-31', 321.00),
(15, 114, 6002, 'Star Labs', '2019-01-03', 223.00),
(16, 115, 6002, 'Star Labs', '2019-01-05', 179.00),
(17, 116, 5001, 'Wayne Enterprises', '2019-01-14', 180.00),
(18, 117, 7003, 'Daily Planet', '2019-01-21', 162.00),
(19, 118, 5001, 'Wayne Enterprises', '2019-02-02', 133.00),
(20, 119, 7003, 'Daily Planet', '2019-02-05', 55.00),
(21, 120, 5001, 'Wayne Enterprises', '2019-02-08', 25.00),
(22, 121, 6002, 'Star Labs', '2019-02-08', 222.00)
;
So with a single query, I know how to get my data grouped by customer_number, month and year such as:
SELECT customer_number, customer_name, COUNT(id) AS order_count, SUM(order_amount) AS order_amount_sum, MONTH(order_date) AS order_date_month, YEAR(order_date) AS order_date_year
FROM orders_test
GROUP BY customer_number, MONTH(order_date), YEAR(order_date)
;
Which results in:
But how can I get the year-month columns to be created dynamically and efficiently? My data sets could be in the tens of thousands.
Furthermore, I know the max range of dates is 24 months, so there won't me more than 24 columns for the orders in scope.
