CODEWITHSUNDEEP
sqlite
debtitor
debtitor

Reputation: 189

Individual SELECT statement outputs are correct, when JOINed the output is incorrect

I have tried lowercase and UPPERCASE to fix the problem, both have failed.

I have a banking dataset from the FDIC.

When I run each SELECT statement separately I receive the correct output: 

SELECT
AVG(PCR.lnlsdepr) as NetLoansAndLeasesToDeposits
FROM 
All_Reports_20160331_Performance_and_Condition_Ratios as 
PCR

Output:(77%)

76.6238024035116

The second SELECT statement run separately is also correct (e.g. It outputs the amount of money in bank accounts with less than $250,000 as $5.2 Trillion: 

SELECT
sum(CAST(LD.IDdepsam as int)) AS 
DepositAccountsWith$LessThan$250k
FROM 
'All_Reports_20160331_Deposits_Based_on_the_
$250,000_Reporting_Threshold' as LD

Correct Output: ($5,216,146,035,000, $5.2 Trillion)

5216146035

However when I combine the two queries into one query, the output, of the second input, is incorrect. e.g. It outputs the total amount of money in USA bank accounts with less than $250,000 as $31 quintillion, instead of $5.2 Trillion. 

SELECT
AVG(PCR.lnlsdepr) as NetLoansAndLeasesToDeposits,
sum(CAST(LD.IDdepsam as int)) AS 
DepositAccountsWith$LessThan$250k
FROM 
'All_Reports_20160331_Deposits_Based_on_the_$250,000_ 
 Reporting_Threshold' as LD
JOIN 
All_Reports_20160331_Performance_and_Condition_Ratios 
as PCR

Output: enter image description here

I tried converting everything to lowercase, but still received the incorrect output: 

select 
avg(pcr.lnlsdepr) as 'NetLoansAndLeASesToDeposits',
sum(cast(ld.IDdepsam as int)) as 
'DepositAccountsWith$LessThan$250k'
from 
'All_Reports_20160331_Deposits_BASed_on_the_
$250,000_Reporting_Threshold' as ld
join 
'All_Reports_20160331_Performance_and_Condition_Ratios' 
as pcr

Output (incorrect): enter image description here

I tried UPPERCASE and still received an incorrect output: enter image description here

Upvotes: 0

Views: 39

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

The problem is that you have multiple rows in each table and they are multiplying.

SELECT x.NetLoansAndLeasesToDeposits, y."DepositAccountsWith$LessThan$250k"
FROM (SELECT AVG(PCR.lnlsdepr) as NetLoansAndLeasesToDeposits
      FROM 
All_Reports_20160331_Performance_and_Condition_Ratios as 
PCR
     ) x CROSS JOIN
     (SELECT sum(CAST(LD.IDdepsam as int)) AS 
"DepositAccountsWith$LessThan$250k"
      FROM 
"All_Reports_20160331_Deposits_Based_on_the_
$250,000_Reporting_Threshold" LD
     ) y;

Upvotes: 1

forpas
forpas

Reputation: 164099

Why are you joining the 2 datasets?
The results you need can be calculated separately since they are not related and presented into 1 row like this:

SELECT 
  (SELECT AVG(lnlsdepr)
  FROM All_Reports_20160331_Performance_and_Condition_Ratios) 
  AS NetLoansAndLeasesToDeposits,
  (SELECT sum(CAST(IDdepsam as int))
  FROM 'All_Reports_20160331_Deposits_Based_on_the_$250,000_Reporting_Threshold') 
  AS DepositAccountsWith$LessThan$250k

This way you don't need the join overhead.

Upvotes: 1

Related Questions