Calculate AVG by type of product and group by month

Copper Contributor

Hi all, 

Any help how can i get the result on below table based on image attaches?Cap.PNG

 

The pretented result:

MonthAVG (LeadTimeOfTypeFL)AVG (LeadTimeOfTypePH)
1715
21525

 

Thank you

4 Replies
hi,
you can use this code

SELECT Month,
MAX(CASE WHEN ProductType='FL' THEN avg END) "AVG LeadTimeOFFL",
MAX(CASE WHEN ProductType='PH' THEN avg END) "AVG LeadTimeoFPH"
FROM (
SELECT MONTH(DATE) Month,ProductType,AVG(LeadTime) avg FROM ProductTable GROUP BY MONTH(DATE),ProductType
) as fdfdf
GROUP BY Month;

use your table name instead of 'ProductTable' as table name
Month 2: So year 2024 vs 2025 don't matter for the result?
don't matter for the result.it consider on the basis of months

@Arshad440 

This should work for you:

SELECT MONTH(T.Date) AS Month,
       AVG(CASE WHEN T.ProductType = 'FL' THEN T.LeadTime ELSE NULL END) AS LeadTimeOfTypeFL,
       AVG(CASE WHEN T.ProductType = 'PH' THEN T.LeadTime ELSE NULL END) AS LeadTimeOfTypePH
FROM yourTable AS T
GROUP BY MONTH(T.Date)