How to get count and sum with multiple parameter in case when-CodePudding

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;

CodePudding user response：

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.

CodePudding user response：

I think a stored procedure would be a way to achieve this. In this procedure you will have to do 3 queries for 1, 6 and 9 months. like

SELECT COUNT(ID) as cnt_id, SUM(price) as sum_price INTO 1_month_count, 1_month_sum WHERE status = 'Hold' OR status = 'Done' AND TIMESTAMPDIFF(MONTH, old_date, curr_date) >= 1 AND  TIMESTAMPDIFF(MONTH, old_date, curr_date) < 6;

then you return your variables 1_month_count etc. by

SELECT @1_month_count, @1_month_sum; /*and all others*/

More information about store procedures with OUT parameters can be found here: https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html