CODEWITHSUNDEEP
sqloracle-database
Ahmed Selim
Ahmed Selim

Reputation: 99

Make Case When in select sum query

I have a query like this:

SELECT COUNT(DISTINCT ACCOUNT_CODE),  ZERO_DAYS_FLAG FROM (
SELECT  SUM (CLASS_BELOW_BALANCE) AS ZERO_DAYS_FLAG, ACCOUNT_CODE
FROM ACCOUNT_BALANCES 
WHERE MERCHANT_STATUS != 'Suspended'
AND MERCHANT_CSP_CODE IN ('1' , '2' , '3' , '4' , '5' , '6' , '7')
AND CHECK_BALANCE_DATE BETWEEN '01-SEP-15' AND '30-SEP-15' 
GROUP BY ACCOUNT_CODE) GROUP BY ZERO_DAYS_FLAG ORDER BY ZERO_DAYS_FLAG;

and the result in:

COUNT(DISTINCTACCOUNT_CODE) ZERO_DAYS_FLAG         
--------------------------- ---------------------- 
2026                        0                      
2036                        1                      
2005                        2                      
1874                        3                      
2020                        4                      
2074                        5                      
2224                        6                      
2343                        7                      
2460                        8                      
2666                        9                      
2606                        10                     
2511                        11                     
2571                        12

What i need is adding a case when the ZERO_DAYS_FLAG is greater than 6 then the result to be summed as 6 days like this:

COUNT(DISTINCTACCOUNT_CODE) ZERO_DAYS_FLAG         
--------------------------- ---------------------- 
2026                        0                      
2036                        1                      
2005                        2                      
1874                        3                      
2020                        4                      
2074                        5                      
2224                        6+

Upvotes: 1

Views: 65

Answers (2)

Bohemian
Bohemian

Reputation: 425428

Use this CASE expression:

CASE WHEN ZERO_DAYS_FLAG > 5 THEN '+6' ELSE CAST(ZERO_DAYS_FLAG AS CHAR) END

Like this:

SELECT
  COUNT(DISTINCT ACCOUNT_CODE),
  CASE WHEN ZERO_DAYS_FLAG > 5 THEN '6+' ELSE CAST(ZERO_DAYS_FLAG AS CHAR) END ZERO_DAYS_FLAG
FROM (
  SELECT SUM(CLASS_BELOW_BALANCE) AS ZERO_DAYS_FLAG, ACCOUNT_CODE
  FROM ACCOUNT_BALANCES 
  WHERE MERCHANT_STATUS != 'Suspended'
  AND MERCHANT_CSP_CODE IN ('1' , '2' , '3' , '4' , '5' , '6' , '7')
  AND CHECK_BALANCE_DATE BETWEEN '01-SEP-15' AND '30-SEP-15' 
  GROUP BY ACCOUNT_CODE) X
GROUP BY CASE WHEN ZERO_DAYS_FLAG > 5 THEN '6+' ELSE CAST(ZERO_DAYS_FLAG AS CHAR) END
ORDER BY CASE WHEN ZERO_DAYS_FLAG > 5 THEN '6+' ELSE CAST(ZERO_DAYS_FLAG AS CHAR) END;

Note: The value "6+" in the original query has been changed to "+6" to allow it to still be recognised as an integer in case the result is also being used by an application. All credit to OP for this idea.

Upvotes: 2

Brad
Brad

Reputation: 2320

SELECT 
    COUNT(DISTINCT ACCOUNT_CODE),  
    CASE WHEN ZERO_DAYS_FLAG > 6 THEN '6+' ELSE ZERO_DAYS_FLAG || '' END AS zero_days_flag 
FROM (
     SELECT  
         SUM (CLASS_BELOW_BALANCE) AS ZERO_DAYS_FLAG, ACCOUNT_CODE
     FROM ACCOUNT_BALANCES 
     WHERE MERCHANT_STATUS != 'Suspended'
     AND MERCHANT_CSP_CODE IN ('1' , '2' , '3' , '4' , '5' , '6' , '7')
     AND CHECK_BALANCE_DATE BETWEEN '01-SEP-15' AND '30-SEP-15' 
     GROUP BY ACCOUNT_CODE) 
  GROUP BY ZERO_DAYS_FLAG ORDER BY ZERO_DAYS_FLAG;

Upvotes: 0

Related Questions