Mysql: How to Query 1 table with 2 different conditions and display result in separate columns-CodePudding

I have 1 table named ItemDelivery. I wanted to get the count of items that has DeliveryDate and the items that has been receivedDate per month. Some items deliveryDate month have different receiveDate month such as items scheduled for delivery on the later part of the month would be received on early days of succeeding month. Some may take months to be delivered for overseas.

This is the data:

id  iditem  deliveryDate    receiveDate
1   2       2021-01-03      2021-01-05
2   2       2021-01-03  
3   3       2021-02-05      2021-02-06
4   5       2021-02-05  
5   4       2021-02-20      2021-03-01
6   3       2021-03-15      2021-04-08

I would like to have

Mo  Delivery Recieve
Jan   2        1
Feb   3        1
Mar   1        1
Apr   0        1

This query gives 1 columns only

select date_format(deliveryDate,'%b') as mo ,
       count(id) as delivery 
from ItemDelivery 
where year(deliveryDate)=2021
group by month(deliveryDate)

union all

select date_format(receiveDate,'%b') as mo ,
       count(id) as received 
from ItemDelivery 
where year(receiveDate)=2021
group by month(receiveDate)

Output:

Mo  Delivery
Jan 2
Feb 3
Mar 1
Jan 1
Feb 1
Mar 1
Apr 1

This query also have different output

SELECT d1.mo, d1.delivery, d2.received
    FROM   
    (SELECT month(deliveryDate) as mo, count(id) AS delivery
    FROM   ItemDelivery
    WHERE  year(deliveryDate)=2021 group by month(deliveryDate)) as d1,
    (SELECT month(receiveDate) as mo, count(id) AS received
    FROM   ItemDelivery
    WHERE  year(receiveDate)=2021 group by month(receiveDate)) as d2

Output:

mo  delivery    received
1   2       1
2   3       1
3   1       1
1   2       1
2   3       1
3   1       1
1   2       1
2   3       1
3   1       1
1   2       1
2   3       1
3   1       1

This has also the same output except if I use condition d1.mo=d2.mo:

select d1.mo, d1.delivery, d2.received
from 
(SELECT month(deliveryDate) as mo, count(id) as delivery
FROM   ItemDelivery
WHERE  year(deliveryDate)=2021 group by month(deliveryDate)) d1


     inner join 
     (SELECT month(receiveDate) as mo, count(id) as received
FROM   ItemDelivery
WHERE  year(receiveDate)=2021 group by month(receiveDate)) d2

Any suggestions ?

CodePudding user response：

SELECT
  date_format(eventDate,'%b') AS mo,
  SUM(delivery)               AS delivery,
  SUM(receive)                AS receive
FROM
(
  SELECT deliveryDate AS eventDate, 1 AS delivery, 0 AS receive FROM ItemDelivery
  UNION ALL
  SELECT receiveDate  AS eventDate, 0 AS delivery, 1 AS receive FROM ItemDelivery
)
  AS rotated
WHERE
      eventDate >= '2021-01-01'
  AND eventDate <  '2022-01-01'
GROUP BY
  month(eventDate)