I want to write a query in Postgres where I need to calculate the total of a numeric field.
Example- Table Students

Table Marksheet

And I need the output of the query like this –

NOTE: I will remove the repeated Totals and group the class data using the UI.
I have written a query to get these data like this –

function getStudentMarks(limit numeric, offset numeric)
with filter_data as (
    select
        'Total' as total,
        st.class as class,
        st.id as id,
        st.name as name,
        st.mark as mark
    from Students st
    Limit limit Offset offset
    ) 
    (select
        'Total' as total,
        st.class as class,
        st.id as id,
        st.name as name,
        st.mark as mark
    from filter_data
    )
    Union all
    (
        select
            'Total' as total,
            st.class as class,
            null as id,
            null as name,
            sum(st.mark) as mark
        from filter_data
        group by 
            st.class
    )
    Union all
    (
        select
            'Total' as total,
            st.class as class,
            null as id,
            null as name,
            sum(st.mark) as mark
        from filter_data
    )

But this affects the pagination total, that is when the limit is 5, in the first only the sum of 5 rows is calculated but my expectation is I want the sum of all data on the first page itself, which can be achieved by giving the Limit and Offset condition in the first final select query, but since the Student table is big data table I couldn’t get an optimized query (NOTE: I will join must join multiple tables so I have limited the output of parent table).

Expected –
In the first page

On the second page –

Output of my query

On the second page –

Could we able to get the cumulative sum of the field in another flow? could anyone help me