How to do sum in SQL

Question

question

Durga26 asked Jan 20, '21

How to do sum in SQL

Hi,

I have a table called Loan and have columns with percentage and amount. Eg :

ID Percentage Amount

1 10 10000

2 10 10000

3 15 15000

4 10 10000

The total loan approved for customer is 25%.

So I want to write a query to only display the lines from the table where percentage sum till reached 25%. The query should return ID 1 2 and 3 only. How to do it?

I write until this but return error :

Declare @PurchaserLoanAmount Integer = 25

Select Percentage, Amount

From Loan

Where Sum(StagePercentage) <= @PurchaserLoanAmount

Order By Stage

sqlserver2012 sql2012 sql2014

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-01-20T12:21:38Z

anthony.green answered Jan 20, '21

A couple of points in the query, what is "StagePercentage"? you only have percentage

What is Stage? You don't have stage in the sample data ( I suspect that this is the ID column).

It sounds like you need a running total query on the percentage but then if the allowed percentage is 25, why would you allow them to have 35% of the loan if you include IDs 1,2,3

A rather basic example would be something like the below

create table #loan (id int, percentage int, amount int)
insert into #loan values
(1, 10, 10000),
(2, 10, 10000),
(3, 15, 15000),
(4, 10, 10000)


declare @LoanApprovedAmount INT = 25, @CurrentStageForAmount INT

;with cteLoan as
(
select 
*
,sum(percentage) over (order by id) as runningpercent
from #loan
)
select 
    @CurrentStageForAmount = min(id) 
from
    cteLoan
where
    runningpercent >= @LoanApprovedAmount

SELECT * FROM #loan where ID <= @CurrentStageForAmount

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question