CODEWITHSUNDEEP
sqloracle-database
Sanjay
Sanjay

Reputation: 443

Not a single-group group function while using case when statements

In a query I am trying to use case when keywords. I have to check for 3 conditions there. But I am getting Not a single-group group function error. Any syntax error in my query? Please guide.

Query is 

SELECT  
        CASE WHEN DIST_TYPE_ID IN (5033,5034,5035,5036) 
        THEN MIN (b2b_start_dt +NVL(access_lead_Days,0))
        ELSE 
             CASE WHEN MAX(overridden) = 0 
             THEN NVL (MIN (src_start_dt), MIN (b2b_start_dt)) 
             ELSE MAX(B2B_START_dT) END 
        END as start_date 

FROM    prog_access_movie_v         

WHERE   trunc(SYSDATE) BETWEEN b2b_start_dt AND b2b_end_dt      
        AND (user_id IS NOT NULL OR GROUP_ID IS NOT NULL)        
        AND dist_type_id IN (5034) AND prog_id = (432899)

Upvotes: 0

Views: 3536

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

This is the select:

SELECT  (CASE WHEN DIST_TYPE_ID IN (5033, 5034, 5035, 5036) 
              THEN MIN(b2b_start_dt + NVL(access_lead_Days, 0))
              ELSE (CASE WHEN MAX(overridden) = 0 
                         THEN NVL(MIN(src_start_dt), MIN(b2b_start_dt)) 
                         ELSE MAX(B2B_START_dT)
                    END)
         END) as start_date

In terms of aggregation, everything is fine except for the condition on DIST_TYPE_ID. If you had:

SELECT  (CASE WHEN MAX(DIST_TYPE_ID) IN (5033, 5034, 5035, 5036) 
              THEN MIN(b2b_start_dt + NVL(access_lead_Days, 0))
              ELSE (CASE WHEN MAX(overridden) = 0 
                         THEN NVL(MIN(src_start_dt), MIN(b2b_start_dt)) 
                         ELSE MAX(B2B_START_dT)
                    END)
         END) as start_date

Or:

SELECT  (CASE WHEN SUM(CASE WHEN DIST_TYPE_ID IN (5033, 5034, 5035, 5036) THEN 1 ELSE 0 END) > 0
              THEN MIN(b2b_start_dt + NVL(access_lead_Days, 0))
              ELSE (CASE WHEN MAX(overridden) = 0 
                         THEN NVL(MIN(src_start_dt), MIN(b2b_start_dt)) 
                         ELSE MAX(B2B_START_dT)
                    END)
         END) as start_date

Or a myriad of other possibilities, then the query should parse and execute.

Upvotes: 2

Sanjay
Sanjay

Reputation: 443

It was a silly mistake from me . Group by dist_type_id worked out.

SELECT  CASE WHEN DIST_TYPE_ID IN (5033,5034,5035,5036) THEN MIN (b2b_start_dt +NVL(access_lead_Days,0))
 ELSE CASE WHEN MAX(overridden) = 0 THEN NVL (MIN (src_start_dt), MIN (b2b_start_dt)) ELSE MAX(B2B_START_dT) END END as start_date

 FROM prog_access_movie_v         
 WHERE trunc(SYSDATE) BETWEEN b2b_start_dt AND b2b_end_dt       
 AND (user_id IS NOT NULL OR GROUP_ID IS NOT NULL)        
 AND dist_type_id IN (5034) AND prog_id = (432899)
 group by access_lead_days, dist_type_id

Upvotes: 0

Related Questions