CODEWITHSUNDEEP
oracle-databaseplsql
user3111964
user3111964

Reputation: 1

sum Data from column 1 where falls between range in column 2

I'm fairly new to PL-SQL so could use a bit of help.

Table#1 contains:

LoanIntersestRates
------------------
4.5
4.0
3.5
3.0
2.5

Table #2 Contains:

ActualInterestRate  LoanAmt
-----------------------------
4.6                 356258.00
4.7                 387958.25
2.6                 485658.25
3.65                500562.00
4.1                 434135.25
2.65                756254.02
4.5                 286325.02

What I need to do is get a sum of the loanAmt where the ActualInterestRate is Exactly what is in table one. Also, Need to sum up the loadAmts where the actualInterestrate is 1-50 points above each of the LoanInterestRates, 50-100 points above each of the LoanInterestRates and 100+ points above each of the LoanInterestRates.

Any help on this would be greatly appreciated. Thanks in advance!

Upvotes: 0

Views: 74

Answers (2)

Jasti
Jasti

Reputation: 947

Use the following query if you want to get the sum for rates mentioned in table 1

select lr.interest,sum(amount) from loaninterestrates lr, loaninterestamounts la
where lr.interest = la.interest
group by lr.interest

For step - 2 to find the sum for ranges use this

select la1.intrange,sum(la1.amount)from
(
select la.interest,la.amount,case when Remainder(la.interest*10,10) < 0 or Remainder(la.interest*10,10) = 5 
                  then to_char(FLOOR(la.interest) + 0.5) || '-' || to_char(FLOOR(la.interest) + 1.0)
            else to_char(FLOOR(la.interest)) || '-' || to_char(FLOOR(la.interest) + 0.5 )
            end as intrange
        from loaninterestamounts la

  ) la1
 group by la1.intrange

sqlfiddle here: http://sqlfiddle.com/#!4/92f15/8

Upvotes: 1

Galbarad
Galbarad

Reputation: 453

hi I have some misunderstanding your question but please check my sql

with t1 as
 (select 4.5 as lir
    from dual
  union all
  select 4.0
    from dual
  union all
  select 3.5
    from dual
  union all
  select 3.0
    from dual
  union all
  select 2.5 from dual),
t2 as
 (select 4.6 as air, 356258.00 as la
    from dual
  union all
  select 4.7, 387958.25
    from dual
  union all
  select 2.6, 485658.25
    from dual
  union all
  select 3.65, 500562.00
    from dual
  union all
  select 4.1, 434135.25
    from dual
  union all
  select 2.65, 756254.02
    from dual
  union all
  select 4.5, 286325.02 from dual),
t1_d as
 (select lir as lir_low, lead(lir) over(order by lir) as lir_high from t1)
select t1_d.lir_low, sum(la) as sum_la
  from t1_d, t2
 where t2.air >= t1_d.lir_low
   and ((t2.air < t1_d.lir_high) or (t1_d.lir_high is null))
 group by t1_d.lir_low

Upvotes: 0

Related Questions