3 ways to display sum of columns total in gridview footer using asp .net

In this asp .net tutorial we will learn how to display sum of columns total in gridview footer using asp .net while paging enabled. We will use three methods to display sum of column in gridview footer:

1. Using Loop.
2. Using LINQ.
3. Using different SQL query.

1. Create a database with some records.

CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Age] [int] NULL,
[Salary] [float] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([Id], [Name], [Email], [Age], [Salary]) VALUES (2, N'Max', N'[email protected]', 14, 2500)
INSERT [dbo].[Employee] ([Id], [Name], [Email], [Age], [Salary]) VALUES (3, N'Viso', N'[email protected]', 14, 4500)
INSERT [dbo].[Employee] ([Id], [Name], [Email], [Age], [Salary]) VALUES (4, N'Tony', N'[email protected]', 25, 8000)
INSERT [dbo].[Employee] ([Id], [Name], [Email], [Age], [Salary]) VALUES (5, N'Bruce', N'[email protected]', 45, 15000)
INSERT [dbo].[Employee] ([Id], [Name], [Email], [Age], [Salary]) VALUES (6, N'Jack', N'[email protected]', 25, 18000)
SET IDENTITY_INSERT [dbo].[Employee] OFF
    

2. Create Gridview.

        
            
                
                    Sno
                
                
                    <%#(Container.DataItemIndex+1)%>
                
            
            
            
            
        
    

Here first columns is used to show serial number in gridview. For more information you can visit: How to show row number in Gridview, Repeater,ListView, FormView, DetailsView and DataList

3. Add connection stirng to web.config file.

For more information about connection strings in asp .net visit: Connection string example in asp net

4. Add namespaces.

Add following namespaces.

using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
    

5. Method1 to display sum of columns total in gridview footer.

In this method we will do a loop within records present in dataset and then we will display total in gridview footer.

     public void bindData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        string cmdText = "SELECT * FROM Employee;
        SqlDataAdapter adp = new SqlDataAdapter(cmdText, con);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        grdTest.DataSource = ds;
            
        double totalSalary = 0;
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            totalSalary += Convert.ToDouble(dr["Salary"]);
        }

        //--- Here 3 is the number of column where you want to show the total.  
        grdTest.Columns[3].FooterText = totalSalary.ToString();

        //--- Make sure you bind gridview after writing total into footer.
        grdTest.DataBind();

        con.Close();
        adp.Dispose();
        ds.Dispose();
    }

6. Method2 to display sum of columns total in gridview footer.

In this method we use LINQ to get total of records and then we will display total in gridview footer.

     public void bindData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        string cmdText = "SELECT * FROM Employee;
        SqlDataAdapter adp = new SqlDataAdapter(cmdText, con);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        grdTest.DataSource = ds;
    
       
        //--- Here 3 is the number of column where you want to show the total.  
        grdTest.Columns[3].FooterText = ds.Tables[0].AsEnumerable().Select(x => x.Field("Salary")).Sum().ToString();

        //--- Make sure you bind gridview after writing total into footer.
        grdTest.DataBind();

        con.Close();
        adp.Dispose();
        ds.Dispose();
    }

7. Method3 to display sum of columns total in gridview footer.

In this method we will use two different select statements. one for getting all records and second to get sum of salary column.

     public void bindData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        string cmdText = "SELECT * FROM Employee; SELECT SUM(salary)AS totalSalary FROM Employee";
        SqlDataAdapter adp = new SqlDataAdapter(cmdText, con);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        grdTest.DataSource = ds;
    
       
        //--- Here 3 is the number of column where you want to show the total.  
        grdTest.Columns[3].FooterText = ds.Tables[1].Rows[0]["totalSalary"].ToString();
        
        //--- Make sure you bind gridview after writing total into footer.
        grdTest.DataBind();

        con.Close();
        adp.Dispose();
        ds.Dispose();
    }

8. Bind Gridview on Page Load.

protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
            bindData();
        }
}
     

Display sum of columns total in gridview footer demo