SQLTeam.com | Weblogs | Forums

Group By Doesn't Seem to Be Working

#1

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!

0 Likes

#2

replace "in.loantype" in the "group by" with the case statement and remove the distinct

0 Likes

#3

Thank you very much. I am 100% certain I was provided that direction prior as well. Worked like a charm.

0 Likes