SQL - Case expression : How can I use multiple conditions within a single WHEN

Question

I want to use multiple conditions within a single WHEN but it return error 'not a single-group group function' I use Oracle SQL Developer

SELECT

CASE WHEN (TB2.BENE_CODE NOT LIKE '%ET%') AND (SUM(TB2.APPROVE_TOTAL)
> 0)   THEN SUM(TB2.APPROVED_TOTAL)
     ELSE 0 END AS Total_PayAmount  

FROM clm_header_tr TB1

result is ORA-00937: not a single-group group function

I try to separate condition with () but it doesn't work.

So What is the root cause? How can I resolve it? Thank you.

Answer 1

You shoud sum() around case, not inside:

SELECT 
SUM(CASE 
    WHEN (TB2.BENE_CODE NOT LIKE '%ET%') AND (TB2.APPROVE_TOTAL > 0) 
    THEN TB2.APPROVED_TOTAL
     ELSE 0 END) AS Total_PayAmount  
FROM clm_header_tr TB1

If you want to compare aggregate data and certain field data, then you should firstly do a subquery that uses analytic variant: sum() over (), it will show aggregated data on each field and then you can use it in case.

Answer 2

Generally speaking, it all depends on what you want to get as result. Posting an invalid piece of code (typos, typos ...) without any sample data, rules that should be apply on that and desired result doesn't help us help you.

I created my own sample table and your code - as you know - doesn't work:

SQL> SELECT
  2  CASE WHEN (TB2.BENE_CODE NOT LIKE '%ET%') AND (SUM(TB2.APPROVED_TOTAL) > 0) THEN SUM(TB2.APPROVED_TOTAL)
  3       ELSE 0
  4  END AS Total_PayAmount
  5  FROM clm_header_tr TB2;
CASE WHEN (TB2.BENE_CODE NOT LIKE '%ET%') AND (SUM(TB2.APPROVED_TOTAL) > 0) THEN SUM(TB2.APPROVED_TOTAL)
           *
ERROR at line 2:
ORA-00937: not a single-group group function

If you include a column being involved into a group by clause, code works, but - is it what you need? Back to square one: sample data, rules, desired result.

SQL> SELECT
  2  CASE WHEN (TB2.BENE_CODE NOT LIKE '%ET%') AND (SUM(TB2.APPROVED_TOTAL) > 0) THEN SUM(TB2.APPROVED_TOTAL)
  3       ELSE 0
  4  END AS Total_PayAmount
  5  FROM clm_header_tr TB2
  6  GROUP BY tb2.bene_code;

TOTAL_PAYAMOUNT
---------------
           1300
           5000
           2450

SQL>

Answer 3

Look at the code below:

WITH    --  S a m p l e    D a t a :
    tbl (ID, BENE_CODE, APPROVED_TOTAL) AS
        (   Select 1, 'AET01',       10 From Dual Union All
            Select 2, 'AET02',       50 From Dual Union All
            Select 3, 'AFT01ASD',    90 From Dual Union All 
            Select 4, 'CKT01',      111 From Dual Union All
            Select 5, 'ET',         200 From Dual 
        )
-- Your code :      
SELECT    CASE WHEN (BENE_CODE NOT LIKE '%ET%') AND (SUM(APPROVED_TOTAL) > 0)
               THEN SUM(APPROVED_TOTAL)
          ELSE 0 
          END AS Total_Pay_Amount  
FROM      tbl
--  ORA-00937: not a single-group group function

1. total payed amount for BENE_CODE NOT LIKE '%ET%'

SELECT    Sum(CASE WHEN (BENE_CODE NOT LIKE '%ET%') 
               THEN APPROVED_TOTAL
          ELSE 0 
          END) AS Total_Pay_Amount  
FROM      tbl
--  TOTAL_PAY_AMOUNT
--  ----------------
--               201

2. Total Payed Amounts for BENE_CODE Containing 'ET' and not containing 'ET'

Select      Case    When BENE_CODE LIKE '%ET%' Then 'ET_CONTAINED'
            Else 'ET_NOT_CONTAINED'
            End  "BENE_CODE_ET_STAT", 
            Sum(APPROVED_TOTAL) AS Total_Pay_Amount  
From        tbl
Group By    Case    When BENE_CODE LIKE '%ET%' Then 'ET_CONTAINED'
            Else 'ET_NOT_CONTAINED'
            End
--  BENE_CODE_ET_STA TOTAL_PAY_AMOUNT
--  ---------------- ----------------
--  ET_CONTAINED                  260
--  ET_NOT_CONTAINED              201   

You get the ORA-00937: not a single-group group function error if you use a not aggregated column(s) in selection list without grouping your result by that column(s).
Depending on what you want to get there are two Select commands that work.
1st - select Sum(Case expression resulting value) - this is aggregated column so you don't need the group by - results with a single row
2nd - select two sums - there is Case expression in Select list - and the same Case in Group By clause - results with 2 rows...
Regarding your question about multiple conditions in Case expressions - you can use it as anywhere else - below is an example (same as 1st option above):

SELECT    Sum(CASE WHEN BENE_CODE NOT LIKE '%ET%' And APPROVED_TOTAL > 0 
               THEN APPROVED_TOTAL
          ELSE 0 
          END) AS Total_Pay_Amount  
FROM      tbl
--  TOTAL_PAY_AMOUNT
--  ----------------
--               201