Union Query for Summary Total

1

Hello.

I am trying to get a summary total using the query below, but am met w/ 'Conversion Failed when converting the varchar value 'Total' to data type int.'

Select
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription,
Total_Loans=Count(ln.LoanKey),
Total_OrignalAmount=SUM(ln.OriginalAmount),
Pay_Off = SUM(Case When ah.PrincipalAmount<0 Then ah.PrincipalAmount *-1 Else 0 End)

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

Where
ln.Closed=-1
AND Ln.LoanType IN ('7','27','42','43','1','4','45','47')
AND ln.CloseDate Between '10/01/2019' AND '10/31/2019'
AND ah.EntryDate Between '10/01/2019' AND '10/31/2019'

Group By
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription

Union

Select
'Total' As LoanType,
''As LoanTypeINCDE,
''As LoanTypeDescription,
Total_Loans=Count(ln.LoanKey),
Total_OrignalAmount=SUM(ln.OriginalAmount),
Pay_Off = SUM(Case When ah.PrincipalAmount<0 Then ah.PrincipalAmount *-1 Else 0 End)

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

Where
ln.Closed=-1
AND Ln.LoanType IN ('7','27','42','43','1','4','45','47')
AND ln.CloseDate Between '10/01/2019' AND '10/31/2019'
AND ah.EntryDate Between '10/01/2019' AND '10/31/2019'

Where am I going astray? Thank you.

2

LoanType is an integer. Since you want 'Total' to appear in that column, you'll need to change the int to be varchar.

Select
CAST(ln.LoanType As varchar(10)) AS LoanType,
...
Union All
Select
'Total' As LoanType,
...

3

Thanks for the response.

I tried what you suggested and was met with ...
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Maybe it was the way I updated it.

Select
CAST(ln.LoanType As varchar(10)) AS LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription,
Total_Loans=Count(ln.LoanKey),
Total_OrignalAmount=SUM(ln.OriginalAmount),
Pay_Off = SUM(Case When ah.PrincipalAmount<0 Then ah.PrincipalAmount *-1 Else 0 End)

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

Where
ln.Closed=-1
AND Ln.LoanType IN ('7','27','42','43','1','4','45','47')
AND ln.CloseDate Between '10/01/2019' AND '10/31/2019'
AND ah.EntryDate Between '10/01/2019' AND '10/31/2019'

Group By
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription

Union All

Select
'Total' As LoanType

4

I meant "..." to mean "the rest of the query as it appeared before"; like this:

...
Union All

Select
'Total' As LoanType,
''As LoanTypeINCDE,
''As LoanTypeDescription,
Total_Loans=Count(ln.LoanKey),
Total_OrignalAmount=SUM(ln.OriginalAmount),
Pay_Off = SUM(Case When ah.PrincipalAmount<0 Then ah.PrincipalAmount *-1 Else 0 End)

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

Where
ln.Closed=-1
AND Ln.LoanType IN ('7','27','42','43','1','4','45','47')
AND ln.CloseDate Between '10/01/2019' AND '10/31/2019'
AND ah.EntryDate Between '10/01/2019' AND '10/31/2019'
5

lol. OMG. Sorry about that.

Thank you for your help. I am all set.