which MySQL function can be use to have the count of Orders and shipments with respect to Month?-CodePudding

I have Data which indicates month of Order date and month of Shipment date. I want to convert the records which will show, in each month, what is the count of orders and in same month what is the count of shipments

Because I am a beginner to SQL I could not try any way but this is the expected table.

I want to make this happen with Select statement. Please refer the image for the data by clicking here Data with expected result

CodePudding user response：

Your question text is a bit unspecific but it's tagged "mysql" - so I assume this is what you use.

Something like this would work (please replace with exact column/table names):

SELECT 
  YEAR(order_date), 
  MONTH(order_date), 
  COUNT(order_date) AS order_count, 
  SUM(CASE WHEN MONTH(order_date) = MONTH(shipment_date) THEN 1 ELSE 0 END) AS shipped_count

FROM orders 
GROUP BY YEAR(order_date), MONTH(order_date)

CodePudding user response：

Looks like there is a lack of additional information in your question, but maybe you would need something like this

SELECT Month(order_date) as Month, order_count, shipment_count 
SUM (order_count) 
FROM orders 
GROUP BY order_date;