Grouping data rows into 1000's

Question

I want to generate a post_no column and split my data into groups, so that every 1000 posts can be grouped in a set.

For example, if I have 3222 total posts in region A and 4174 posts in region B, I want the posts grouped into 1000's like so:

post_no      id  region amount 
0001 - 1000  1    A      1000
1001 - 2000  2    A      1000
2001 - 3000  3    A      1000
3001 - 3222  4    A       222
0001 - 1000  5    B      1000
1001 - 2000  6    B      1000
2001 - 3000  7    B      1000
3001 - 4000  8    B      1000
4001 - 4174  9    B       174

I'd prefer to use sql or python code to implement this.

Answer 1

UPDATE:

I've just re-read the question, which still doesn't make a lot of sense, but I've concluded that I'm not answering the question being asked here, but I shall leave this here for the time being in case it helps the OP in some small way.

---

So I think I figured out what you were looking for. And I've made a working sample by changing your data slightly. So where you have post_no values that look like this: 110001~111000 I've modified in my sample to be: 1000, as you've not provided an explanation to why these values show like this and how we can use them.

This sample gives you the basic operation to get your desired amount values based on whether or not the value is divisible by 1000.

Reference:

% (Modulus) (Transact-SQL)

Returns the remainder of one number divided by another.

Sample

CREATE TABLE #temp
( post_no INT, id INT, region NVARCHAR(1));

INSERT INTO #temp
( post_no, id, region)
VALUES
(1000, 1, 'A'),
(2000, 2, 'A'),
(3000, 3, 'A'),
(3222, 4, 'A'),
(1000, 5, 'B'),
(2000, 6, 'B'),
(3000, 7, 'B'),
(4000, 8, 'B'),
(4174, 9, 'B');

SELECT t.post_no,
       t.id,
       t.region,
       CASE WHEN t.post_no % 1000 = 0 
            THEN post_no / ROW_NUMBER() OVER (PARTITION BY t.region ORDER BY id) 
            ELSE t.post_no % 1000 
            END AS amount      
FROM #temp AS t;

DROP TABLE #temp;

Result:

post_no     id          region amount
----------- ----------- ------ --------------------
1000        1           A      1000
2000        2           A      1000
3000        3           A      1000
3222        4           A      222
1000        5           B      1000
2000        6           B      1000
3000        7           B      1000
4000        8           B      1000
4174        9           B      174

Note:

This could be further simplified to simply return 1000 where the number is divisible by 1000, but I'll leave the above in there to be safe. Alternate code would look like this:

SELECT t.post_no,
       t.id,
       t.region,
       CASE WHEN t.post_no % 1000 = 0 
            THEN 1000 -- just return 1000 if it's divisible by 1000
            ELSE t.post_no % 1000 
            END AS amount      
FROM #temp AS t;