CODEWITHSUNDEEP
sqlsql-serversum
ChuiFeng
ChuiFeng

Reputation: 23

How to get 'sum' less than one value in sql

Test statement ：

create table #t
(
  isid int primary key identity(1,1),
  amount decimal(18,2)
)
go

insert into #t values(23.43)
insert into #t values(213.43)
insert into #t values(523.3)
insert into #t values(23.4)
insert into #t values(263.23)
insert into #t values(223.43)

drop table #t

How to get 'sum' less than one value（ex.500） order by isid? there has one statement 

select * from #t as a where exists(select 1 from #t where isid<=a.isid having sum(amount)<500)

but it has a bug. When first value more than 500 and second value is negative number.

ex: 

insert into #t values(503.43)
insert into #t values(-13.43)

the result will only has one value(-13.43).

Upvotes: 2

Views: 3604

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

If you are on SQL-Server version, you can use the enhancement of the OVER () clause:

WITH cte AS
( SELECT
      isid,
      SUM(Amount) OVER (ORDER BY isid
                        ROWS BETWEEN UNBOUNDED PRECEDING
                                 AND CURRENT ROW
                       ) AS sum_amount
  FROM t
)
SELECT MAX(isid)
FROM cte
WHERE sum_amount < 500 ;

Upvotes: 0

Michael Bowersox
Michael Bowersox

Reputation: 1481

To accomplish this, I think we can generate running totals for all values in our set and pick the maximum "isid" that has a sum less than 500.

    create table T
    (
      isid int primary key identity(1,1),
      amount decimal(18,2)
    )
    go

    insert into T values(503.43)
    insert into T values(-13.43)
    insert into T values(5.00)
    insert into T values(1.00)
    insert into T values(55.00)

SELECT isid
FROM T
WHERE isid <= 
(
  SELECT MAX(B.isid)
  FROM
    (
      SELECT
      T1.isid,
      T1.Amount,
      SUM(T2.Amount) AS SummedAmount
      FROM T T1
      INNER JOIN T T2 ON T2.isid <= T1.isid
      GROUP BY T1.isid, T1.Amount
      HAVING SUM(T2.Amount) < 500
    ) B
)

Here is a SQLFiddle: http://www.sqlfiddle.com/#!3/968af/4

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Here is a way to get the isid with the largest cumulative sum less than 500, assuming that the isid's are numbered sequentially with no gaps:

select minisid - 1
from (select MIN(isid) as minisid
      from (select *, (select SUM(amount) from #t t2 where t2.isid <= t.isid) as cumsum
            from #t t
           ) t
      where cumsum >= 500
     ) t

If you want to return no rows in the second case, then add the clause:

where minisid > 1

Upvotes: 0

ChuiFeng
ChuiFeng

Reputation: 23

@Lamak what happens if in your first test data you insert a -800 as the last row? the result img

Upvotes: 0

Related Questions