This is a silly question but I need help on this.

I have a table of 50 rows. Each one has customer transaction data.

I want to create a stored procedure where if I send a customer name parameter, I want to select all the the transactions for that customer and sum the amount of transactions in groupings of 5.

So for example,

Customer1 05/15/2004 $10,000
Customer1 05/15/2004 $10,000
Customer1 05/15/2004 $10,000
Customer1 05/15/2004 $10,000
Customer1 05/15/2004 $10,000

Customer1 05/15/2004 $10,000
Customer1 05/15/2004 $10,000

So the logic is the first set of 5 have a sum > $30000 INSERT into exception
The second, although the same customer, is ok.

How do you get a grouping as such in SQL?

Thanks for the help.

no, it's not a silly question, but it has a very easy answer

there is absolutely no such thing as position within a table

so, how were you planning on dividing your rows into groups of five?

Does your table have an identity column by chance (this is just a numeric column that assigns a unique number to each row)?

Yes...I have each row with an id.

that's helpful

which rows would you like to group together, seeing as how they all presumably have different ids?

please state response in terms of id

Are you looking for something like this?

--Setup a sample transactions table.
DECLARE @transactions TABLE(
transaction_id INT IDENTITY(1,1) PRIMARY KEY,
customer_id VARCHAR(55),
transaction_date DATETIME,
transaction_amount MONEY)

INSERT @transactions(customer_id, transaction_date, transaction_amount)
SELECT 'Customer1','05/15/04',10000 UNION ALL
SELECT 'Customer1','05/15/04',10000 UNION ALL
SELECT 'Customer1','05/15/04',10000 UNION ALL
SELECT 'Customer1','05/15/04',10000 UNION ALL
SELECT 'Customer1','05/15/04',10000 UNION ALL
SELECT 'Customer1','05/15/04',10000 UNION ALL
SELECT 'Customer1','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000 UNION ALL
SELECT 'Customer2','05/15/04',10000

--Verify it's what you want.
SELECT * FROM @transactions

--****This actually begins the query.
--Declare the grouping number.
DECLARE @grouping INT

--This is the number you want to group your transactions by.
SELECT @grouping = 6

--This is an example of what this grouping number provides you.
SELECT (1-1)/@grouping
SELECT (2-1)/@grouping
SELECT (3-1)/@grouping
SELECT (4-1)/@grouping
SELECT (5-1)/@grouping
SELECT (6-1)/@grouping
SELECT (7-1)/@grouping
SELECT (8-1)/@grouping
SELECT (9-1)/@grouping
SELECT (10-1)/@grouping
SELECT (11-1)/@grouping
SELECT (12-1)/@grouping
SELECT (13-1)/@grouping
SELECT (14-1)/@grouping
SELECT (15-1)/@grouping
SELECT (16-1)/@grouping
SELECT (17-1)/@grouping
SELECT (18-1)/@grouping
SELECT (19-1)/@grouping
SELECT (20-1)/@grouping

--Create a temp table and insert so all customer_ids are grouped together and the ordinal can be used.
DECLARE @transaction_grouping TABLE(
transaction_grouping INT IDENTITY(1,1) PRIMARY KEY,
customer_id VARCHAR(55),
transaction_date DATETIME,
transaction_amount MONEY)

INSERT @transaction_grouping(
customer_id,
transaction_date,
transaction_amount)

SELECT
customer_id,
transaction_date,
transaction_amount
FROM
@transactions
ORDER BY
customer_id,
transaction_date

SELECT
(tg.transaction_grouping-mp.min_point)/@grouping AS group_id,
tg.customer_id,
tg.transaction_date,
SUM(tg.transaction_amount) AS transaction_charge
FROM
@transaction_grouping tg
INNER JOIN (
SELECT MIN(transaction_grouping) AS min_point, customer_id, transaction_date
FROM @transaction_grouping
GROUP BY
customer_id,
transaction_date) mp ON tg.customer_id = mp.customer_id
AND tg.transaction_date = mp.transaction_date
GROUP BY
(tg.transaction_grouping-mp.min_point)/@grouping,
tg.customer_id,
tg.transaction_date
ORDER BY
tg.customer_id,
tg.group_id