How to get sum and count based on month interval in R-CodePudding

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 Old_Date and Curr_Date.

Required Output-

ID     1_Month_Done   6_Month_Done    9_Month_Done   Highest_Price
ID-1   0              2               3              5000

CodePudding user response：

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;