Using field in CASE without group by

Question

I have the following code and I am having trouble figuring out how to NOT include these into the group by. Some of the arguments are purely for the case and that is all. I can't include them in the group by. I cam't really group by anything else as I need to get the counts by TransactionTyp only but I keep getting this error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Maybe the use of CASE is the wrong way to go about this? But I need to replace some of the values based on other fields.

This is in MS SQL too.

SELECT  Count(*), 
    CASE 
        WHEN a.TransactionTyp IS NOT NULL THEN a.TransactionTyp
        WHEN a.ClaimStatus = 'Resolved-Deflected' THEN 'Deflected'
        WHEN a.ClaimStatus = 'Resolved-NoAction' THEN 'Deflected'
        WHEN a.ClaimStatus = 'Open' AND b.AssignOperator = 'PendClaim.NF_POS_Pinless' THEN 'Affiliate'
    END As TransactionTyp
FROM Table1 a
LEFT JOIN Table2 b
    ON a.ClaimNbr = b.ClaimDisputeNbr AND b.CreateDte = Convert(varchar,GetDate(), 101)
WHERE a.ClaimEntryDte = Convert(varchar,GetDate(),101)    
AND a.ClaimTypCd  IN ('DEBS', 'DEBSI', 'DBCIN', 'DBCUS')
GROUP BY TransactionTyp

Answer 1

One problem might be that you can't refer to aliases in the group by clause. Try to repeat the case definition in the group by:

GROUP BY
    CASE 
        WHEN a.TransactionTyp IS NOT NULL THEN a.TransactionTyp
        WHEN a.ClaimStatus = 'Resolved-Deflected' THEN 'Deflected'
        WHEN a.ClaimStatus = 'Resolved-NoAction' THEN 'Deflected'
        WHEN a.ClaimStatus = 'Open' AND b.AssignOperator = 'PendClaim.NF_POS_Pinless' THEN 'Affiliate'
    END As TransactionTyp

Alternatively, use a subquery to define the alias:

select  TransactionTyp
,       count(*)
from    (
        SELECT  CASE 
                WHEN a.TransactionTyp IS NOT NULL THEN a.TransactionTyp
                WHEN a.ClaimStatus = 'Resolved-Deflected' THEN 'Deflected'
                WHEN a.ClaimStatus = 'Resolved-NoAction' THEN 'Deflected'
                WHEN a.ClaimStatus = 'Open' AND b.AssignOperator = 'PendClaim.NF_POS_Pinless' THEN 'Affiliate'
                END As TransactionTyp
        FROM    Table1 a
        LEFT JOIN 
                Table2 b
        ON      a.ClaimNbr = b.ClaimDisputeNbr 
                AND b.CreateDte = Convert(varchar,GetDate(), 101)
        WHERE   a.ClaimEntryDte = Convert(varchar,GetDate(),101)    
                AND a.ClaimTypCd  IN ('DEBS', 'DEBSI', 'DBCIN', 'DBCUS')
        )
GROUP BY 
        TransactionTyp

Answer 2

Without adding create_on on group by how to get current Date result or current month result

select segment_name, 
cast(SUM(case when t1.create_on=GETDATE()) then actual_total_sale    as currentDateData,
cast(SUM(case when MONTH(create_on) = MONTH(getDate()) then actual_total_sale    as currentMonthData,
 from 
PR_DAILY_AOP_TARGET t1
where is_active=1 group by segment_name