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'
Advertisement
Answer
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'
orto_date
function with appropriate format mask:to_date('29.12.2021', 'dd.mm.yyyy')