I have the 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-05-26 2021-09-30 13:15:15 In Progress 4500 1
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 the above table, I need to fetch the count & sum of instances where status is Done and Hold in the last 1, 6, and 9-month intervals.
The interval needs to be calculated between Old_Date and Curr_Date.
Required Output-
ID 1_Month_Count 6_Month_Count 9_Month_Count 1_Month_Sum 6_Month_Sum 9_Month_Sum
ID-1 0 2 4 0 5000 12800
I have tried following query but It is not working.
SELECT ID,
SUM(CASE WHEN TIMESTAMPDIFF(month, Old_Date, Curr_Date) <= 12
WHEN (Status IN ('Done', 'Hold') THEN SUM(ID) ELSE 0 END) AS 12_Month_Done`,
FROM Table
Group BY ID;
If you want in the results only the IDs that match your conditions then use conditional aggregation after filtering the table:
SELECT ID,
COUNT(CASE WHEN Old_Date >= Curr_Date - INTERVAL 1 month THEN 1 END) AS `1_Month_Count`,
COUNT(CASE WHEN Old_Date >= Curr_Date - INTERVAL 6 month THEN 1 END) AS `6_Month_Count`,
COUNT(*) AS `9_Month_Count`,
SUM(CASE WHEN Old_Date >= Curr_Date - INTERVAL 1 month THEN Price ELSE 0 END) AS `1_Month_Sum`,
SUM(CASE WHEN Old_Date >= Curr_Date - INTERVAL 6 month THEN Price ELSE 0 END) AS `6_Month_Sum`,
SUM(Price) AS `9_Month_Sum`
FROM tablename
WHERE Status IN ('Done', 'Hold') AND Old_Date >= Curr_Date - INTERVAL 9 month
GROUP BY ID;
If you want all the IDs even if they don't match the conditions:
SELECT ID,
COUNT(CASE WHEN Old_Date >= Curr_Date - INTERVAL 1 month
AND Status IN ('Done', 'Hold') THEN 1 END) AS `1_Month_Count`,
COUNT(CASE WHEN Old_Date >= Curr_Date - INTERVAL 6 month
AND Status IN ('Done', 'Hold') THEN 1 END) AS `6_Month_Count`,
COUNT(CASE WHEN Old_Date >= Curr_Date - INTERVAL 9 month
AND Status IN ('Done', 'Hold') THEN 1 END) AS `9_Month_Count`,
SUM(CASE WHEN Old_Date >= Curr_Date - INTERVAL 1 month
AND Status IN ('Done', 'Hold') THEN Price ELSE 0 END) AS `1_Month_Sum`,
SUM(CASE WHEN Old_Date >= Curr_Date - INTERVAL 6 month
AND Status IN ('Done', 'Hold') THEN Price ELSE 0 END) AS `6_Month_Sum`,
SUM(CASE WHEN Old_Date >= Curr_Date - INTERVAL 9 month
AND Status IN ('Done', 'Hold') THEN Price ELSE 0 END) AS `9_Month_Sum`
FROM tablename
GROUP BY ID;
See the demo.