I have following table in MySQL (Version – 5.7.18-log).
ID Old_Date Curr_Date Status Price items ID-1 2021-07-14 2021-09-30 13:15:15 Done 1500 3 ID-1 2021-06-26 2021-09-30 13:15:15 Hold 3500 6 ID-1 2021-03-04 2021-09-30 13:15:15 Done 5000 3 ID-1 2021-01-11 2021-09-30 13:15:15 Done 2800 2
From above table, I need to fetch the count of
IDs where status is
Done in last 1, 6 and 9 month interval. Also need to fetch highest
Price in last 12 month.
The interval needs to be calculated between
ID 1_Month_Done 6_Month_Done 9_Month_Done Highest_Price ID-1 0 2 3 5000
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
You could use conditional aggregation here:
SELECT ID, SUM(TIMESTAMPDIFF(month, Old_Date, Curr_Date) <= 1) AS 1_Month_Done, SUM(TIMESTAMPDIFF(month, Old_Date, Curr_Date) <= 6) AS 6_Month_Done, SUM(TIMESTAMPDIFF(month, Old_Date, Curr_Date) <= 9) AS 9_Month_Done, MAX(Price) AS Highest_Price FROM yourTable GROUP BY ID;