question

Durga26 avatar image
Durga26 asked

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


sqlserver2012sql2012sql2014
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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