SQLTeam.com | Weblogs | Forums

Duplicates ... How to Correct

Sigh. I am once again running into issues with duplicates.

This is what I am getting when I run the query below. I want to get the count/sum by Product. Where am I going wrong?

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= Case When ah.TransactionType='LDNC' Then Count(ah.AccountSequenceNumber) Else '0' End,
Checks_Dollar= Case When ah.TransactionType='LDNC' Then SUM(ah.TransactionAmount) Else '0' End,
Widget_Number= Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then Count(ah.AccountSequenceNumber) Else '0' End,
Widget_Dollar= Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then SUM(ah.TransactionAmount) Else '0' End,
Phone_In_Person_Number= Case When ah.TransactionType='CHK' AND ah.FocusTellerId <>'6009' Then Count(ah.AccountSequenceNumber) Else '0' End,
Phone_In_Person_Dollar= Case When ah.TransactionType='CHK' AND ah.FocusTellerID <>'6009' Then SUM(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
ah.TransactionType,
ah.FocusTellerId,
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

Solved ... so sorry for the bother.

Checks_Number= Count(Case When ah.TransactionType='LDNC' Then ah.AccountSequenceNumber Else '0' End)

I was trying to Count the case statement with a Count within it. Just removed the Count and it worked.