troubleshooting Question

Pervasive SQL syntax

Avatar of Sheldon Livingston
Sheldon LivingstonFlag for United States of America asked on
SQL
5 Comments1 Solution707 ViewsLast Modified:
Imagine I have Table A with one record.  The "Mortgage" field contains 10,000.

Table B (other loans) has two records (because this one guy has a second and third mortgage). The "Amount" fields contain $300 and $500.

When I use a left outer join to add up the Mortgage and Amount fields I get 20,800.

I assume that this is because the left outer join returns 2 records... 10,000 and 300 as well as 10,000 and 500.

SELECT 
  "ID" AS "ID",
  "strName" AS "Borrower Name",
  SUM(("Mortgage" + IF("Amount" IS NOT NULL,"Amount",0))) AS "Total Owed"

FROM 
  "TableA"
LEFT OUTER JOIN
  "TableB"

ON
  "MLoan" = "OLoan"

GROUP BY 
  "ID", "strName"

ORDER BY
  "ID"

How can I get this to return 10,800?
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros