Case in select... 1

[handle87]

Hi,
I am a little new at this type of programming and wondered if someone could let me know what I am doing wrong here... I am getting the following errors:

Server: Msg 8120, Level 16, State 1, Line 32
Column '_EnrollmentsByCSR$$$_Cancelled_Details_V.ChrgStatus' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 32
Column '_EnrollmentsByCSR$$$_Cancelled_Details_V.ChrgAmt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 32
Column '_EnrollmentsByCSR$$$_Cancelled_Details_V.ChrgStatus' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 32
Column '_EnrollmentsByCSR$$$_Cancelled_Details_V.ChrgAmt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 32
Column '_EnrollmentsByCSR$$$_Cancelled_Details_V.ChrgAmt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

when I run the view _EnrollmentsByCSR$$$_Cancelled_Details_V it does return those values.

SELECT
PymntCurrency,
StaffID,
CASE
WHEN ChrgStatus = 'O' AND ChrgAmt > 0 THEN
sum(ChrgAmt)
ELSE
sum(0)
END As WriteOffAmt,
CASE
WHEN ChrgStatus = 'Z' and ChrgAmt > 0 THEN
sum(ChrgAmt)
ELSE
sum(0)
END As InstallmentsAmt,
CASE
WHEN ChrgAmt < 0 THEN
sum(0 - ChrgAmt)
ELSE
sum(0)
END As CreditAmt

FROM
_EnrollmentsByCSR$$$_Cancelled_Details_V
GROUP BY
PymntCurrency,
StaffID


I am trying to convert this code from the Access that it was created in.

Any idea whats wrong with this qery?

 

[amrita418]

Try this:

SELECT 
    PymntCurrency, 
    StaffID,
    sum(CASE
        WHEN ChrgStatus = 'O' AND ChrgAmt > 0 THEN
            ChrgAmt
        ELSE
            0
    END) As WriteOffAmt,
    sum(CASE
        WHEN ChrgStatus = 'Z' and ChrgAmt > 0 THEN
            ChrgAmt
        ELSE 0
    END) As InstallmentsAmt,
    sum(CASE
        WHEN ChrgAmt < 0 THEN
             -ChrgAmt
        ELSE
           0
    END) As CreditAmt
    
FROM
    _EnrollmentsByCSR$$$_Cancelled_Details_V
GROUP BY 
    PymntCurrency, 
    StaffID

Regards,
AA

 

[cmmrfrds]

SELECT
PymntCurrency,
StaffID,
max(CASE
WHEN ChrgStatus = 'O' AND ChrgAmt > 0 THEN
sum(ChrgAmt)
ELSE
sum(0)
END) As WriteOffAmt,
max(CASE
WHEN ChrgStatus = 'Z' and ChrgAmt > 0 THEN
sum(ChrgAmt)
ELSE
sum(0)
END) As InstallmentsAmt,
max(CASE
WHEN ChrgAmt < 0 THEN
sum(0 - ChrgAmt)
ELSE
sum(0)
END) As CreditAmt

FROM
_EnrollmentsByCSR$$$_Cancelled_Details_V
GROUP BY
PymntCurrency,
StaffID

 

[handle87]

Thank you for the suggestions!!

I ended up using the first one and it worked... I had tried the SUM() around the CASE already but it was
'-ChrgAmt' that you suggested that was causing the problem...

thanks again!

 

[amrita418]

Glad I could help.

Regards,
AA