MySql Pivot Table with Count-CodePudding

I have the following table holding customer orders.

order_id  |  customer_id  |  fulfilled |  order_date
---------------------------------------------------
123       |  1234         |  1         |  2022/01/12
126       |  1235         |  1         |  2022/01/18
127       |  1235         |  0         |  2022/01/19
128       |  1236         |  1         |  2022/01/01
129       |  1236         |  0         |  2022/01/03
130       |  1236         |  1         |  2022/01/04
131       |  1237         |  1         |  2022/01/01
132       |  1237         |  1         |  2022/01/03
133       |  1237         |  1         |  2022/01/04
134       |  1238         |  1         |  2022/02/12
135       |  1239         |  1         |  2022/02/18
136       |  1239         |  0         |  2022/02/19
137       |  1239         |  1         |  2022/02/20
138       |  1239         |  1         |  2022/02/21
139       |  1240         |  1         |  2022/02/01
140       |  1240         |  1         |  2022/02/03
141       |  1240         |  1         |  2022/02/04
142       |  1241         |  1         |  2022/02/01
143       |  1241         |  0         |  2022/02/03
144       |  1241         |  1         |  2022/02/04
145       |  1241         |  1         |  2022/02/04
146       |  1241         |  1         |  2022/02/04

SQL Below

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `fulfilled` tinyint(1) NOT NULL DEFAULT 0,
  `order_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `orders` (`order_id`, `customer_id`, `fulfilled`, `order_date`) VALUES
(123, 1234, 1, '2022-01-12'),
(126, 1235, 1, '2022-01-18'),
(127, 1235, 0, '2022-01-19'),
(128, 1236, 1, '2022-01-01'),
(129, 1236, 0, '2022-01-03'),
(130, 1236, 1, '2022-01-04'),
(131, 1237, 1, '2022-01-01'),
(132, 1237, 1, '2022-01-03'),
(133, 1237, 1, '2022-01-04'),
(134, 1238, 1, '2022-02-12'),
(135, 1239, 1, '2022-02-18'),
(136, 1239, 0, '2022-02-19'),
(137, 1239, 1, '2022-02-20'),
(138, 1239, 1, '2022-02-21'),
(139, 1240, 1, '2022-02-01'),
(140, 1240, 1, '2022-02-03'),
(141, 1240, 1, '2022-02-04'),
(142, 1241, 1, '2022-02-01'),
(143, 1241, 0, '2022-02-03'),
(144, 1241, 1, '2022-02-04'),
(145, 1241, 1, '2022-02-04'),
(146, 1241, 1, '2022-02-04');

ALTER TABLE `orders` ADD PRIMARY KEY (`order_id`);

I would like to create a MySQL query to arrange this data into a pivot table showing how many customers had 1, 2, 3 or 4 fulfilled orders (fullfilled column = 1) for each month. There will never be more than 4 orders per customer.

               Customers        Customers         Customers         Customers 
Month/Year  |  with 1 Order  |  with 2 Orders  |  with 3 Orders  |  with 4 Orders   
-------------------------------------------------------------------------------------------
Jan 22      |  2             |  2              |   1             |  0   
Feb 22      |  1             |  0              |   2             |  1

I have looked online extensively and failed miserably at this task. Any assistance would be appreciated.

CodePudding user response：

We can do a two step aggregation. First, aggregate by customer and month to get the various counts per customer in each month. Then aggregate by month and pivot to get the final result.

SELECT month_year,
       SUM(total = 1) AS one_order,
       SUM(total = 2) AS two_order,
       SUM(total = 3) AS three_order,
       SUM(total = 4) AS four_order,
FROM
(
    SELECT DATE_FORMAT(order_date, '%b %y') AS month_year, customer_id,
           SUM(fulfilled) AS total
    FROM orders
    GROUP BY 1, 2
) t
GROUP BY month_year;