Sum of GridView column values in footer in Asp.Net

Last Updated: 28th April 2024

In this article I am going to show you how to display a summary or the sum of values in a particular column, in the GridView footer row. Also known as running total of a column, these accumulated figures needs to be displayed below all the pages, if GridView Paging is set as True.

See this demo

We have come across many examples, where the total values shown at the bottom are the sum of the values of the active page. That is, page 1 will show running total of the 1st page; page 2 will display the total of 2nd page etc.

Using ViewState [""], we can add and display a Grand Total either on every page’s footer or the last page itself. This may or may be the best solution, but it works without any issues.

The working Solution

First, create an SQL table and name it as Books. We will bind the GridView with the table’s data. This table has three columns namely the "BookID, BookName and Price". We will display the total price per page along with the Grand total. (See image)

Create Table (Books)

Using SQL Server

CREATE TABLE [dbo].[Books](
    [BookID] [int] IDENTITY(1,1) NOT NULL,
    [BookName] [varchar](50) NULL,
    [Price] [numeric](18, 2) NULL,
    PRIMARY KEY CLUSTERED 
    ( [BookID] ASC ) ON [PRIMARY]
)

Add few rows of data in it, so you can set the GridView Paging as true. In fact you need more rows, at least 10 rows and set the GridView page size to ‘5’, so you can have ‘2’ pages to check for the running totals in each page with the Grand Total.

PageSize="5"

The Markup and GridView

<!DOCTYPE html>
<html>
<body>
<form id="form1" runat="server">
<div style="width:500px">
        
    <asp:GridView ID="grd" runat="server" Width="100%" 
        AutoGenerateColumns="false"
        AlternatingRowStyle-BackColor="#E9ECF1" 
        HeaderStyle-BackColor="white" 
        RowStyle-HorizontalAlign="Center" 
        RowStyle-Height="22" 
        HeaderStyle-Height="25"
        FooterStyle-HorizontalAlign="Center" 
        FooterStyle-Font-Bold="true" 
        FooterStyle-ForeColor="#555555"
        ShowFooter="true" 
        AllowPaging="true" 
        PageSize="5" 
        OnPageIndexChanging = "grd_PageIndexChanging" 
        OnRowDataBound = "grd_RowDataBound">
                
        <Columns>
            <asp:TemplateField HeaderText="Book ID">
                <ItemTemplate><%#Eval("BookID")%></ItemTemplate>
            </asp:TemplateField>
                    
            <asp:TemplateField HeaderText="Name of the Book">
                <ItemTemplate><%#Eval("BookName")%></ItemTemplate>

                <FooterTemplate>
                    <div style="padding:0 0 5px 0"><asp:Label Text="Page Total" runat="server" /></div>
                    <div><asp:Label Text="Grand Total" runat="server" /></div>
                </FooterTemplate>
            </asp:TemplateField>
                    
            <asp:TemplateField HeaderText="Price ($)">
                <ItemTemplate><asp:Label ID="lblTotalPrice" runat="server" Text='<%#Eval("Price")%>'>
                    </asp:Label></ItemTemplate>

                <FooterTemplate>
                    <div style="padding:0 0 5px 0"><asp:Label ID="lblPageTotal" runat="server" /></div>
                    <div><asp:Label ID="lblGrandTotal" runat="server" /></div>
                </FooterTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</div>
</form>
</body>
</html>

Before we proceed further with our example, few things that I thought would be important for you to understand about the GridView design properties.

1) I have set ShowFooter="true". This example has a running total at the footer of each row.

2) GridView paging is set as AllowPaging="true", also the size of page is set as '5' (PageSize="5").

3) Events, such as OnPageIndexChanging and OnRowDataBound are declared.

4) Finally we have 'two' footer templates under the 2nd and 3rd columns each. The 3rd column’s footer will display the values for Page total and Grand Total respectively. <FooterTemplate></FooterTemplate>

See this demo

C# Code

using System;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    Decimal dPageTotal;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { 
            BindData(); 
        }
    }
    private void BindData()
    {
        DataTable dt = new DataTable();

        using (SqlConnection con = new SqlConnection("Data Source=DNA;" +
            "Persist Security Info=False;" +
            "Integrated Security=SSPI;" +
            "Initial Catalog=DNA_Clasified;User Id=;Password=;Connect Timeout=500;"))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "SELECT *FROM Books";
                con.Open();

                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                sda.Fill(dt);

                // Calculate the "total price" and store the value in a ViewState.
                if (ViewState["TotalPrice"] == null)
                {
                    Decimal dPrice = 0;
                    for (int i = 0; i <= dt.Rows.Count - 1; i++)
                    {
                        dPrice += Convert.ToDecimal(dt.Rows[i]["price"]);
                    }
                    ViewState["TotalPrice"] = dPrice;
                }
                // Bind query result with the Grid.
                grd.DataSource = dt; 
                grd.DataBind();
            }
        }
    }

    // GridView paging.
    protected void grd_PageIndexChanging(object sender, 
        System.Web.UI.WebControls.GridViewPageEventArgs e)
    {
        grd.PageIndex = e.NewPageIndex;
        BindData();
    }

    // Calculate and show page total and grand total while binding the rows.
    protected void grd_RowDataBound(object sender, 
        System.Web.UI.WebControls.GridViewRowEventArgs e)
    {
        // Get the running total of price for each page.
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            Label lblPgTotal = (Label)e.Row.FindControl("lblTotalPrice");
            dPageTotal += Decimal.Parse(lblPgTotal.Text);
        }

        // Finally, show the running and grand total on each page.
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            if (ViewState["TotalPrice"] != null && dPageTotal != 0)
            {
                // Page total.
                Label lblPageTotal = (Label)e.Row.FindControl("lblPageTotal");
                lblPageTotal.Text = dPageTotal.ToString("N2");

                // Grand total.
                Label lblGrandTotal = (Label)e.Row.FindControl("lblGrandTotal");
                lblGrandTotal.Text = ViewState["TotalPrice"].ToString();
            }
        }
    }
}

Option Explicit On
Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Dim dPageTotal As Double = 0

    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) 
        Handles form1.Load

        If Not IsPostBack Then
            Me.BindData()
        End If
    End Sub

    Private Sub BindData()
        Dim dt As DataTable = New DataTable
        Using con As SqlConnection = _
            New SqlConnection ("Data Source=dna;Persist Security Info=False;" & _
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;")

            Dim strQuery As String = "SELECT *FROM Books"

            Using cmd As SqlCommand = New SqlCommand(strQuery)
                Dim sda As SqlDataAdapter = New SqlDataAdapter
                
                cmd.Connection = con : con.Open()
                sda.SelectCommand = cmd
                sda.Fill(dt)

                ' CALCULATE THE TOTAL PRICE AND HOLD THE VALUE IN A "VIEWSTATE".
                If ((ViewState("TotalPrice")) Is Nothing) Then
                    For i = 0 To dt.Rows.Count - 1
                        ViewState("TotalPrice") += dt.Rows(i).Item(2)   ' ITEM 2 IS THE PRICE.
                    Next
                End If

                grd.DataSource = dt : grd.DataBind()
            End Using
        End Using
    End Sub
    
    ' GRIDVIEW PAGING.
    Protected Sub grd_PageIndexChanging(ByVal sender As Object, 
            ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) 
            Handles grd.PageIndexChanging
            
        grd.PageIndex = e.NewPageIndex
        BindData()
    End Sub

    Protected Sub grd_RowDataBound(ByVal sender As Object, 
            ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) 
            Handles grd.RowDataBound
            
        ' GET THE RUNNING TOTAL OF PRICE FOR EACH PAGE.
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim lblTotalPrice As Label = CType(e.Row.FindControl("lblTotalPrice"), Label)
            dPageTotal += lblTotalPrice.Text
        End If

        ' FINALLY, SHOW THE RUNNING AND GRAND TOTAL ON EACH PAGE.
        If e.Row.RowType = DataControlRowType.Footer Then
            If Not (ViewState("TotalPrice") Is Nothing) Then
                ' PAGE TOTAL.
                Dim lblPageTotal As Label = CType(e.Row.FindControl("lblPageTotal"), Label)
                lblPageTotal.Text = Decimal.Parse(dPageTotal).ToString("N2")

                ' GRAND TOTAL.
                Dim lblGrandTotal As Label = CType(e.Row.FindControl("lblGrandTotal"), Label)
                lblGrandTotal.Text = Decimal.Parse(ViewState("TotalPrice")).ToString("N2")
            End If
        End If
    End Sub
End Class

showing page total and grand total in gridview footer

See this demo

← PreviousNext →

Show sum of GridView column values in footer using C# and VB

Using SQL Server