Mysql Query to calculate the distinct count of pickup numbers by plant-CodePudding

I am trying to get the distinct count of pickup numbers by plant and pickupmonth year

Input

pickdate    picknumber  Plant
1/3/2022    L009803 Seattle
1/3/2022    L009803 Seattle
1/3/2022    L009803 Seattle
1/20/2022   L19033  Irving
1/21/2022   KL89231 Mini chih
1/23/2022   MY12341 kettle
1/23/2022   MY12341 kettle
1/25/2022   FD1211  Seattle
1/26/2022   HJ12W1  Irving
2/11/2022   K231245 Irving
2/11/2022   K231245 Irving
2/15/2022   N12ER1  Mini chih
2/21/2022   JS1234  Irving
2/25/2022   MK12E1  Kettle

I am looking to get to the below table to get the distinct count of pickup numbers by plant and timeperiod

Time period     Seattle Irving  Mini chih   Kettle
Jan-22              2     2        1           1
Feb-22              0     2        1           1

Please find the DDL for the input table..

create table input
(pickdate date,
picknumber varchar(40),
plant varchar(20))

insert into input values
('1/3/2022','L009803','Seattle'),
('1/3/2022','L009803','Seattle'),
('1/3/2022','L009803','Seattle'),
('1/20/2022','L19033','Irving'),
('1/21/2022','KL89231','Mini chih'),
('1/23/2022','MY12341','kettle'),
('1/23/2022','MY12341','kettle'),
('1/25/2022','FD1211','Seattle'),
('1/26/2022','HJ12W1','Irving'),
('2/11/2022','K231245','Irving'),
('2/11/2022','K231245','Irving'),
('2/15/2022','N12ER1','Mini chih'),
('2/21/2022','JS1234','Irving'),
('2/25/2022','MK12E1','Kettle')

CodePudding user response：

With conditional aggregation:

SELECT DATE_FORMAT(pickdate, '%b-%y') Time_period,
       COUNT(DISTINCT CASE WHEN plant = 'Seattle' THEN picknumber END) Seattle,
       COUNT(DISTINCT CASE WHEN plant = 'Irving' THEN picknumber END) Irving,
       COUNT(DISTINCT CASE WHEN plant = 'Mini chih' THEN picknumber END) Mini_chih,
       COUNT(DISTINCT CASE WHEN plant = 'kettle' THEN picknumber END) kettle
FROM input
GROUP BY Time_period
ORDER BY STR_TO_DATE(CONCAT('01-', Time_period), '%d-%b-%y');

Or:

SELECT DATE_FORMAT(pickdate, '%Y-%m') Time_period,
       COUNT(DISTINCT CASE WHEN plant = 'Seattle' THEN picknumber END) Seattle,
       COUNT(DISTINCT CASE WHEN plant = 'Irving' THEN picknumber END) Irving,
       COUNT(DISTINCT CASE WHEN plant = 'Mini chih' THEN picknumber END) Mini_chih,
       COUNT(DISTINCT CASE WHEN plant = 'kettle' THEN picknumber END) kettle
FROM input
GROUP BY Time_period
ORDER BY Time_period;

See the demo.