I have a table CREDITS with these columns:

• DAY_OPERATIONAL
• TOTAL_LOAN
• CREDITS_PERCENT

I should multiply each row's TOTAL_LOAN and CREDITS_PERCENT then divide to SUM(TOTAL_LOAN) in that DAY_OPERATIONAL.

My code is not working.

How can I achieve this? How do I need to use group by properly?

SELECT 
    (total_loan * credits_percent) / SUM(total_loan) 
FROM
    credits
WHERE
    day_operational = '29.12.2021'

CodePudding user response：

This is how I understood the question.

tc and tl are here just to show what values were used to compute the final result.

SQL> with credits (day_operational, total_loan, credits_percent) as
  2    (select date '2021-12-01', 100, 1 from dual union all
  3     select date '2021-12-01', 200, 4 from dual union all
  4     --
  5     select date '2021-12-02', 500, 5 from dual
  6    )
  7  select day_operational,
  8         sum(total_loan * credits_percent) tc,
  9         sum(total_loan) tl,
 10         --
 11         sum(total_loan * credits_percent) / sum(total_loan) result
 12  from credits
 13  group by day_operational
 14  order by day_operational;

DAY_OPERAT         TC         TL     RESULT
---------- ---------- ---------- ----------
01.12.2021        900        300          3
02.12.2021       2500        500          5

SQL>

So:

01.12.2021: 100 * 1   200 * 4 = 100   800 =  900
            100   200                     =  300  -->  900 / 300 = 3

02.12.2021: 500 * 5                       = 2500
            500                           =  500  --> 2500 / 500 = 5

As of day_operational: if its datatype is date (should be!), then don't compare it to strings. '29.12.2021' is a string. Use

• date literal (as I did): date '2021-12-29' or

• to_date function with appropriate format mask:

  to_date('29.12.2021', 'dd.mm.yyyy')
  

CodePudding user response：

TOTAL_LOAN * CREDITS_PERCENT / SUM(TOTAL_LOAN)

=

CREDITS_PERCENT * (TOTAL_LOAN / SUM(TOTAL_LOAN)

hence

SELECT
  day_operational
, credits_percent * RATIO_TO_REPORT(total_loan) OVER (PARTITION BY day_operational)
FROM credits

See RATIO_TO_REPORT