Can you have a double case in one line of code in SQL?

Anonymous
2022-08-25T16:11:48.24+00:00

I have a sql query that i need to know if I can do or if cant be done . when i run the code i get an error that Column 'tblUBMInsuranceShare.decInsuranceShare' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
SELECT

'Cost of Insurance' AS strType,

SUM(CASE WHEN dtReport = @EndDate THEN ROUND(mnyInsurance,2)  ELSE 0 END)  *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intDT,  
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END) *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intMTD,  
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END) *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intQTD,  
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END) *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END)AS intYTD,  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Michael Taylor 47,461 Reputation points
    2022-08-25T18:23:22.17+00:00

    This has nothing to do with your case statement. The problem is that you're referencing a column that is not in an aggregate function (SUM, for example) and you are using a GROUP BY clause and it isn't part of that either. Because it is not in either of these it cannot be used in your select statement.

    You didn't post the entire query so we'd only be guessing at specifically where in your query things are going wrong. Again, not really related to the use of case statements at all here.

    0 comments No comments

  2. Erland Sommarskog 100.7K Reputation points MVP
    2022-08-25T21:30:51.86+00:00

    As the Cool dad from Texas says, we don't see your full query. But just guessing, maybe:

    SUM(CASE WHEN dtReport = @EndDate THEN ROUND(mnyInsurance,2)  ELSE 0 END)  *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intDT,  
    

    should be:

    SUM(CASE WHEN dtReport = @EndDate   
             THEN ROUND(mnyInsurance,2)       
             ELSE 0  
        END  *  
        CASE WHEN decInsuranceShare IS NOT NULL   
             THEN 1-decInsuranceShare   
             ELSE 0.60   
        END) AS intDT,  
    

    What I have done beside the reformatting is to change END) *(CASE to read END * CASE. That is, I have removed the left parenthesis which closed the argument list for the SUM() function. The I removed the right paren before CASE, because it is not needed.

    0 comments No comments

  3. CosmogHong-MSFT 22,466 Reputation points Microsoft Vendor
    2022-08-26T01:52:40.25+00:00

    Hi @Jannette Jones

    i get an error t*hat Column 'tblUBMInsuranceShare.decInsuranceShare' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

    The error message has already told you that the Column decInsuranceShare is not contained in either an Aggregate function or the GROUP BY clause.
    In fact, if you examine your code closely, you will find that your SUM() only contains the first Case When statement, while the second Case When statement(which is using Column decInsuranceShare as return value) is outside. That's why the error message says Column decInsuranceShare is not contained in either an aggregate function or the GROUP BY clause.

    Therefore, one solution is put the second Case When statement inside SUM(), because (3+4+5)*0.6 equals 3*0.6+4*0.6+5*0.6.
    Change to SUM((CASE WHEN ... END) *(CASE WHEN ... END)) AS intDT

    Ps: For Case When statements, we don't need to add '( )' , because CaseWhen statements always start with 'Case' and end with 'End' which works like '( )'

    Best regards,
    LiHong

    0 comments No comments