Hello.
Running a query with count and sum and am not getting the desired result. I want to group the totals by the Case Statement.
Select Distinct
LoanProduct= Case When ln.LoanType in ('97','99') Then '43XXX3 - VISA Signature'
When ln.LoanType in ('5','8') Then '43XXX2 - Business Platinum'
When ln.LoanType in ('34','55','105') Then '43XXX3 - Platinum Points'
When ln.LoanType in ('69','89','108','103','104') Then '46XXX6 - Platinum'
Else 'None'
End,
Total_Count=COUNT(ah.AccountSequenceNumber),
Total_Dollar=SUM(ah.TransactionAmount)
From Loan ln
Join AccountHistory ah
On ah.MembershipKey=ln.MembershipKey AND ah.AccountNumber=ln.LoanNumber
Where
ln.LoanType In ('99','97','5','8','34','55','105','69','89','108','103','104')
AND ah.TransactionType In ('CHK','LDNC')
AND ah.EntryDate Between '01/01/2019' AND '03/31/2019'
Group By
ln.LoanType
This is what I am getting:
|43XXX3 - Platinum Points|4|7900.00|
|43XXX3 - Platinum Points|53|137909.15|
|43XXX3 - VISA Signature|31|164652.50|
|43XXX3 - VISA Signature|172|810959.46|
|46XXX6 - Platinum|1|400.00|
|46XXX6 - Platinum|12|27914.73|
|46XXX6 - Platinum|22|70973.95|
I want the totals to count and sum so I get one total for each When.
Where am I going astray? Thanks!