• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Pervasive SQL syntax

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"

Open in new window


How can I get this to return 10,800?
0
classnet
Asked:
classnet
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
YOu could use a union query:

SELECT Sum(U.Amount) as AmountOwed
FROM (
SELECT TableA.Mortage as Amount
FROM TableA
UNION ALL
SELECT TableB.Amount
FROM TableB
) as U
0
 
Kent OlsenData Warehouse Architect / DBACommented:
You want to sum the total in table A and add it to the summed total in table B.

No join is necessary.
0
 
classnetAuthor Commented:
This lead me to the answer... thanks.
0
 
Dale FyeCommented:
The answer I provided does exactly what Kdo recommends.  Did you try my solution?
0
 
classnetAuthor Commented:
Did not... I desired to just eliminate the join.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now