How to group data by summed amount less than x

Question

Having an issue handling this in sql. Have a table of Check Amounts tied to customers. If I want to give them each unique numbers to be paid with, I cannot figure out how to do that with just an update statement and not going line by line(there is a large amount of data)

I have to group by a certain criteria, and send out payments, but they cannot be above $10000.

Table

customer CheckAmount
a   4000
a 5000
a 2001
b 3333
c 8000
d 11000

After my update it should look like :

customer checkamount paymentnumber
a 4000 1
a 5000 1
a 2001 2
b 3333 3
c 8000 4
d 11000 5

Is this possible in just an update statement ?

Are you using MySQL, SQL Server, Oracle, PostgreSQL or something else for your database? Is your data sorted by customers? Shouldn't check #2 be written for 9001 instead of 2001? Why is customer `d` getting a check for 11K when the limit is 10K? — zedfoxus, Jan 20 '21 at 23:14
SQL server. Edited a typo, both should be 2001, . the limit is 10K but if it goes over for an individual line item, I cant do anything about that, it cant be split. the data is just line item invoices with multiple clients, for example I pulled two columns that are in one of the main tables to keep it simple to show. Thanks. — GroGuru, Jan 20 '21 at 23:36
It will be possible to do this using row by row operations. Not sure about a set-based solution. I would look at generating two extra int columns for the customer and payment and then using nested while loops to iterate through these checking and updating the payment number. Not efficient obviously but workable. Alternatively you could try a self join inside a single while loop — Johnny Fitz, Jan 21 '21 at 01:24
Is it possible to get one more column in the source data to determine the recording order by time or by the ordinal number of the payment? — id'7238, Jan 21 '21 at 02:14
the data is coming from really about 20 tables joined. I only included those few columns to make it easier to see if anyone could tell me point blank if it could be done or not. there are id's currently in there. I know it could be done iterating through the loop i just wasn't sure if using floor or something else could accomplish this. been doing sql for many years and I just haven't seen anything exactly like this and I am stumped. easy to do based on row count etc but i just don't know with the grouping < X amount — GroGuru, Jan 21 '21 at 02:55

id'7238 · Accepted Answer · 2021-01-21T15:37:47.130

1

This problem can be solved if there is another column that identifies the transaction by time, like this:

transaction_num     customer    checkamount
1                   a           4000    
2                   a           5000    
3                   a           2001    
4                   b           3333    
5                   c           8000    
6                   d           11000

Then the update statement could be like this:

UPDATE p 
SET p.paymentnumber = agg.new_paymentnumber
FROM payments p
JOIN (
  SELECT *,
    DENSE_RANK() OVER (
      ORDER BY customer,
      -- Only the first payments are not higher than 10,000:
      /* CASE WHEN agg_sum <= 10000 THEN 0 ELSE agg_sum END */
      -- All payments are within 10,000:
      CEILING(agg_sum / 10000)
    ) AS new_paymentnumber
  FROM (
    SELECT *, SUM(checkamount) OVER (
      PARTITION BY customer ORDER BY transaction_num
    ) AS agg_sum
    FROM payments
  ) t 
) agg ON p.transaction_num = agg.transaction_num

fiddle

edited Jan 21 '21 at 15:37

answered Jan 21 '21 at 02:59

id'7238

2,428
1
3
11

That's a really elegant solution using dense_rank and case in order by clause. – zedfoxus Jan 21 '21 at 03:25
I really like that, thanks, but it does appears to be breaking after the first set. The data example I gave didn't have multiple records in any but the first set. When I tried on my data it only grouped the first and then it would add 0's in so I adjusted the code you put on fiddle and it split them individually. Thank you for helping I will try more in the morning to see if I can adjust what you sent to work. – GroGuru Jan 21 '21 at 03:42
create table payments (transaction_num int, customer varchar(20), checkamount int, paymentnumber int); insert into payments (transaction_num, customer, checkamount) select 1, 'a', 4000 union all select 2, 'a', 5000 union all select 3, 'a', 2001 union all select 4, 'b', 3333 union all select 5, 'c', 8000 union all select 6, 'd', 11000 union all select 7 ,'e', 500 union all select 8 ,'e', 500 union all select 9 ,'e', 500 union all select 10 ,'e', 500; – GroGuru Jan 21 '21 at 03:43
There is no grouping by any set. You wrote: `I have to group by a certain criteria`. What exactly is this criterion? Please provide in your question the sample datasets and the desired output that are actually needed to solve the problem. – id'7238 Jan 21 '21 at 05:01
Here is an example with grouping transactions by customer: [fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6266d46a22678258e226ddd22872bb46) – id'7238 Jan 21 '21 at 06:02
By grouping I meant the assignment of the payment number. Using the data you posted actual 1 a 4000 1 2 a 5000 1 3 a 2001 2 4 b 3333 3 5 c 8000 4 6 d 11000 5 7 e 1500 6 8 e 5000 6 9 e 4500 7 10 e 500 8 expected 1 a 4000 1 2 a 5000 1 3 a 2001 2 4 b 3333 3 5 c 8000 4 6 d 11000 5 7 e 1500 6 8 e 5000 6 9 e 4500 7 10 e 500 7 the last one should be 7 as well, but is showing 8 – GroGuru Jan 21 '21 at 14:05
Test this [fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a775d4ec0d68e81a5a05f47f42ad7a27). Here the grouping is also based on the accumulated amount, not just the first part. – id'7238 Jan 21 '21 at 15:19
I'm glad I was able to help. Answer has been updated with a found solution. – id'7238 Jan 21 '21 at 15:40

How to group data by summed amount less than x

1 Answers1