Rows in table with specific sum of a column

Question

I have a table containing some payments looking something like this:

id  |  from  |  to  |  amount
--------------------------
1   |   125  | 135  |  2.4
2   |   123  | 134  |  1.7
3   |   124  | 138  |  4.8
4   |   118  | 119  |  3.9
5   |   56   | 254  |  23.5
...

I need to know if there is a way to make SQL query that would tell me if there is a series of consecutive rows, the amount of which sums up to a certain value. For example, if I wanted value 6.5, it would return rows 2 to 3. If I wanted 12.8, it would return rows 1 to 4 and so on.

I am absolutely stuck and would appreciate some help.

you need to have a loop to check the sum with desired output and then output the rows — Arijit Mukherjee, Jul 02 '14 at 11:55
Just a general point - for a large enough table that could be an extremely long running query, ensure at the very least that you remove any "amount" larger than the value you're searching for. — Kevin Roche, Jul 02 '14 at 12:02

Gordon Linoff · Answer 1 · 2014-07-02T12:22:00.107

3

I would approach this as follows. First, calculate the cumulative sum. Then, the condition that consecutive rows have a particular sum is equivalent to saying that the difference between two of the cumulative sums equals that value.

with p as (
      select p.*, sum(amount) over (order by id) as cumamount
      from payments p
     )
select 
from p p1 join
     p p2
     on p1.id <= p2.id and
       ( p2.cumamount - p1.cumamount ) = 6.5;

As a note: this will probably not work if amount is stored as a floating point number because of very small inaccuracies. If amount where an integer, it would be fine, but it clearly is not. A fixed point representation should be ok.

edited Jul 02 '14 at 12:22

answered Jul 02 '14 at 12:06

Gordon Linoff

1,242,037
58
646
786

I think you need to add a 0 row at the start for this method to work when the set starts at the first row? (although I don't have 2012 to hand, so I may have misinterpreted) – podiluska Jul 02 '14 at 12:18
@podiluska . . . No. But if the set could consist of just one row, then the `<` needs to be a `<=`. I just made that change, despite the fact that the question seems to emphasize rows in the plural. – Gordon Linoff Jul 02 '14 at 12:22
1

For those who've never heard of it before: http://en.wikipedia.org/wiki/Prefix_sum – didierc Jul 02 '14 at 12:32

score 2 · Answer 2 · answered Jul 02 '14 at 12:01

2

;with numbers as (select number from master..spt_values where type='p' and number between 1 and (Select MAX(id) from yourtable)),
ranges as ( select n1.number as start, n2.number as finish from numbers n1 cross join numbers n2 where n1.number<=n2.number)
    select yourtable.* from yourtable
        inner join
        (
            select start, finish
            from ranges
                inner join yourtable on id between start and finish
            group by start, finish
            having SUM(amount)=12.8 
        ) results
    on yourtable.id between start and finish

answered Jul 02 '14 at 12:01

podiluska

50,950
7
98
104

Be careful with that `spt_values` table, it seems to be an undocumented feature: http://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-me – didierc Jul 02 '14 at 12:17
1

It's been there forever, and it likely always will be http://stackoverflow.com/questions/4273978/why-and-how-to-split-column-using-master-spt-values/4280038#4280038 – podiluska Jul 02 '14 at 12:19
1

The more important issue with `spt_values` is that the question doesn't limit the number of rows to 2,047. – Gordon Linoff Jul 02 '14 at 12:38

Rows in table with specific sum of a column

2 Answers2