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

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;

Contents hide

Answers:

Method 1

Method 2

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

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.

Method 2

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

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