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