add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (
`ID` VARCHAR(4),
`Old_Date` DATETIME,
`Curr_Date` DATETIME,
`Status` VARCHAR(11),
`Price` INTEGER,
`items` INTEGER
);

INSERT INTO tablename
(`ID`, `Old_Date`, `Curr_Date`, `Status`, `Price`, `items`)
VALUES
('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');
Records: 5  Duplicates: 0  Warnings: 0
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;
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
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;
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