Count W/ Case Statement

1

Hello.

I am absolutely baffled at this one.

The query below is producing these results. Notice the Count columns are the same for each product, but the sum amounts are different (and correct). Since the conditions are unique of one another, I am stumped at what I am doing wrong. Thank you for your feedback.

image
image.png730×79 4.52 KB

Select
Product= Case When ln.LoanType In ('5','8') Then 'Business Platinum'
When ln.LoanType In ('34','55') Then 'Platinum Points'
When ln.LoanType In ('69','89','103','104','108') Then 'Platinum'
When ln.LoanType In ('97','99') Then 'VISA Signature'
Else 'Other'
End,
Checks_Number= Count(Case When ah.TransactionType='LDNC' Then ah.TransactionAmount Else '0' End),
Checks_Dollar= SUM(Case When ah.TransactionType='LDNC' Then ah.TransactionAmount Else '0' End),
Widget_Number=Count(Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then ah.AccountSequenceNumber Else '0' End),
Widget_Dollar= SUM(Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then ah.TransactionAmount Else '0' End),
Phone_In_Person_Number= Count(Case When ah.TransactionType='CHK' AND ah.FocusTellerId <>'6009' Then ah.AccountSequenceNumber Else '0' End),
Phone_In_Person_Dollar= SUM(Case When ah.TransactionType='CHK' AND ah.FocusTellerID <>'6009' Then ah.TransactionAmount Else '0' End)

From
Loan ln
Join AccountHistory ah
On ah.MemberNumber=ln.MemberNumber AND ah.AccountNumber=ln.LoanNumber

Where
ln.LoanType In ('5','8','34','55','69','89','97','99','103','104','108')
AND ah.TransactionType In ('CHK','LDNC')
AND ah.EntryDate Between '05/01/2019' AND '05/31/2019'

Group By
Case When ln.LoanType In ('5','8') Then 'Business Platinum'
When ln.LoanType In ('34','55') Then 'Platinum Points'
When ln.LoanType In ('69','89','103','104','108') Then 'Platinum'
When ln.LoanType In ('97','99') Then 'VISA Signature'
Else 'Other'
End

2

Any non-NULL value in a column will increment a COUNT, including '0'.

That's one reason I prefer to use SUM(... THEN 1 ELSE 0 END) rather than COUNT, because to me it makes the logic clearer:

...
    Checks_Number= SUM(Case When ah.TransactionType='LDNC' Then 1 Else 0 End),
    Checks_Dollar= SUM(Case When ah.TransactionType='LDNC' Then ah.TransactionAmount Else 0 End),
    Widget_Number= SUM(Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then 1 Else 0 End),
    Widget_Dollar= SUM(Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then ah.TransactionAmount Else 0 End),
    Phone_In_Person_Number= SUM(Case When ah.TransactionType='CHK' AND ah.FocusTellerId <>'6009' Then 1 Else 0 End),
...
3

Thank you very much. That worked like a charm.