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

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

Contents hide

Answers:

Method 1

Answers:

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.

Method 1

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating