How to get subtotal after every 20th row in sql server

[Rohullah]

dear all,

I have 2000 rows in my table and i want to have subtotal after every 20th row could anyone help me i do not know how to do it.

thanks in advance.

[Rabbit]

First, you need a sequential number you can use to number the rows. If you don't have one, you can use the ROW_NUMBER() function or a ranking query.

After you have a sequential number, you can do math on that number to break it up into groups of 20. Then you can use the ROLLUP functionality of SQL Server to get subtotals on that new group field.

A simplified example is shown below

SELECT
	FIELD1,
	SUM(ROWNUM) AS SumOfRownum
	
FROM (
	SELECT 1 AS ROWNUM, 'a' AS FIELD1 UNION ALL
	SELECT 2, 'b' UNION ALL
	SELECT 3, 'c' UNION ALL
	SELECT 4, 'd' UNION ALL
	SELECT 5, 'e' UNION ALL
	SELECT 6, 'f' UNION ALL
	SELECT 7, 'g' UNION ALL
	SELECT 8, 'h' UNION ALL
	SELECT 9, 'i' UNION ALL
	SELECT 10, 'j'
	) AS t

GROUP BY
	ROLLUP(
		(ROWNUM - 1) / 5 + 1,
		FIELD1
	)

FIELD1	SumOfRownum
a	1
b	2
c	3
d	4
e	5
NULL	15
f	6
g	7
h	8
i	9
j	10
NULL	40
NULL	55

[ck9663]

Be careful about this. Depending if you have an index or not and the type of index, your 20th row may varies.

Nevertheless Rabbit's code will work.

You may also try this:

with x
as
(
select 
   rowid = row_number() over (order by UseAColumnThatYouCanUseToSort),
   groupid = row_number() over (order by UseAColumnThatYouCanUseToSort)/20,
   AnyNumericColumn
from YourTable
)
select 
   GroupId, sum(AnyNumericColumn)
from x
group by groupid

Happy Coding!!!


~ CK