Using SUM in a Case Statement

1

Hello.

I am having an issue with a case statement using SUM. I am at a loss and hitting a brick wall.

Any assistance would be appreciated. Thank you.

What I want:
XXXX070 125 966.73 6324.86

What I am getting:
XXXX070 125 0.00 0.00
XXXX070 125 0.00 6324.86
XXXX070 125 966.73 0.00

Here is my code:

Select Distinct
ccs.MemberNumber,
ccs.LoanNumber,
Case When ccs.SR_BalanceTypeIndex='0' AND ccs.SR_RateTypeIndex='0' AND ccs.SequenceNumber='0' Then SUM(ccs.SubLoanRemainingBalance) Else '0' End AS 'Purchase - Standard Rate Balance',
Case When ccs.SR_BalanceTypeIndex='1' AND ccs.SR_RateTypeIndex='0' AND ccs.SequenceNumber='0' Then SUM(ccs.SubLoanRemainingBalance) Else '0' End AS 'Cash Advance - Standard Balance'

From (
Select *, row_number() over(partition by MemberNumber,LoanNumber,SR_BalanceTypeIndex,SR_RateTypeIndex order by LastModifiedDate desc) as row_num
From CreditCardSplitRateDetail
) as ccs

Where
ccs.MemberNumber='XXXX070'
AND ccs.row_num='1'

Group By
ccs.MemberNumber,
ccs.LoanNumber,
ccs.SR_BalanceTypeIndex,
ccs.SR_RateTypeIndex,
ccs.SequenceNumber,
ccs.row_num

2

Change to:

SUM(Case When ccs.SR_BalanceTypeIndex='0' AND ccs.SR_RateTypeIndex='0' AND ccs.SequenceNumber='0' Then ccs.SubLoanRemainingBalance Else '0' End) AS 'Purchase - Standard Rate Balance',

3

Thank you for responding.

Unfortunately I got the same result.

4

Remove the distinct, it isn't needed. Remove the extra columns from the group by, leave only member number and loan number...

5

Thank you so much. That did the trick. I have always been in the habit of using distinct!

6

Sorry - but that is a very bad habit...it causes very expensive sort operations that are not needed, especially when the GROUP also has to perform the same sort in order to group the data.

The problem wasn't the DISTINCT - the problem was the grouping. You were grouping at a lower level than what you were displaying