SQLTeam.com | Weblogs | Forums

Subquery Count Not Grouping

Hello.

I am running a query with the code below. The intent of this query is to get a count by LoanType, LoanTypeINCDE, and LoanTypeDescription. However, it is returning several totals for each.

How do I just get a count for each of the loan types? Thanks.

image

Select
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription,
Purchase_Count_2019 = (Select Sum(Case When ah.TransactionType='DPA' Then 1 Else 0 End) From AccountHistory ah Where ah.MemberNumber=ln.MemberNumber AND ah.AccountNumber=ln.LoanNumber AND ah.EntryDate Between '11/01/2019' AND '11/30/2019')

From
Loan ln
Join LoanTypeDefinitions ltd
On ltd.LoanType=ln.LoanType

Where
ln.LoanType In ('5','8','34','55','69','89','97','99','103','104','105','108')

Hi i think this will help

Purchase_Count_2019 = (Select Sum(Case When ah.TransactionType='DPA' Then 1 Else 0 End) From AccountHistory ah Where ah.MemberNumber=ln.MemberNumber AND ah.AccountNumber=ln.LoanNumber AND ah.EntryDate Between '11/01/2019' AND '11/30/2019')

Make this instead of inline sub query
A separate table
Join
And sum

Select
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription,
Sum(case .......) As purchase_count_2019
From
( Select TransactionType ,
MemberNumber , AccountNumber from AccountHistory where
Where EntryDate Between '11/01/2019' AND '11/30/2019') ah
Join Loan in on

Thank you for responding, I appreciate the feedback.

Since the AccountHistory table is so large, I wanted to run the subquery. I did it the normal way, by joining the tables and it took about 5 minutes to run.

But I got my result. Thanks again.

hi

thats one way to do it

when you have a large SQL Query

the part where it takes a long time
usually put it into Temp tables # tables with indexes

and then use it in Joins

:+1::+1: