Displaying Sum Total in a DataGrid
page 1 of 1
Published: 20 Jul 2004
Unedited - Community Contributed
Abstract
This article explains how to display a sum total of a column (of numeric values) in a Data Grid. This article discusses two methods which are prevalent in the industry.
by Rajeev Gopalakrishnan
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21176/ 17

The original idea and the implementation:

The idea was to use the ItemDataBound event of the DataGrid control, grabbing the corresponding item value of all the rows of the data grid and summing it up. Then we could display the total in the footer. Please see the code snippet below:

PROTECTED As sender myDataGrid_ItemDataBound(ByVal Sub System.Object,
ByVal e As DataGridItemEventArgs) Handles myDataGrid.ItemDataBound        
Select Case e.Item.ItemType
            Case ListItemType.Item, ListItemType.AlternatingItem
                interimTotal += CType(e.Item.Cells(3).Text, Double)
            Case ListItemType.Footer
                e.Item.Cells(2).Text = "TOTAL: "
                e.Item.Cells(3).Text = interimTotal.ToString
        End Select
End Sub

The code snippet shows the event method which is invoked whenever a row is bound with the data from the data source (the binding is not shown and it is assumed that the data source is a DataReader).

Each item in the data grid is one of these types:

  • Item (Regular row)
  • AlternatingItem (Alternating row)
  • Header
  • Footer
  • Separator
  • SelectedItem
  • Pager
  • EditItem

The only items we are interested in are the Item and AlternatingItem types (and Footer of course). So we catch them with a Select Case statement. We need to remember that this method would be invoked for each row of the data grid. Items in a DataGrid control correspond to rows of output. Each Item has an array of Cells. Each column of the row in question is depicted as a Cell. The variable interimTotal is a Private variable in the code behind class. Once the Footer is reached the sum total could be displayed.

An alternative approach

The above solution works really well if the number of rows is small. If the rows are more than, say 500 rows, then we may need to adopt an alternative approach. The reason for this is that there would be a performance penalty as the method is invoked and all the work is done for each and every row of the data reader.

An alternative approach would be to use the DataSet. Using DataSet would be logically appropriate because what we are doing here is holding the data for a moment for manipulating the same. The DataReader, as we all know, is useful for grabbing the data and throwing it out for display.

The code snippet would look like as given below:

myDataGrid.DataSource = ds
myDataGrid.DataBind()
sumTotal = ds.Tables(0).Compute("SUM(amt_txn)", String.Empty).ToString
lblMessage = “TOTAL :” & sumTotal

The code snippet shows the usage of Compute() method of DataTable object. The amt_txn is the column in the DataTable for which we needed the sum total. You could place the lblMessage below the DataGrid.

Conclusion

Before you throw that stone at me, let me defend myself. I know that I could not display the TOTAL in the footer of the DataGrid.  I could, just by looping through the Items collection (of the DataGrid) and checking the ItemType, but that would be the same as the last example.

 

 

 



User Comments

Title: efsdf   
Name: sdfsdfd
Date: 2012-07-05 8:14:07 AM
Comment:
dfdfsdfsdf
Title: How to Add two columns values in Datagdidview   
Name: Vinod kumar
Date: 2011-12-08 3:46:32 AM
Comment:
Hiiii,plz send me full code to display SUM of two columns of Datagridview in Third column......

Thankuu ,
vinod
Title: Display Sum Total of Column on List View   
Name: Bayon-on, Nino Joseph
Date: 2011-08-17 7:10:27 AM
Comment:
I need to get Sum total of numeric data in in column



Private Sub cmdLoad_Click()
Dim itm As ListItem
Itemcount = lv.ListItems.Count
Dim a As String, b As String, C As String
Open App.Path & "Converted.txt" For Input As #1
Do Until EOF(1)
Input #1, a, b, C
Set itm = lv.ListItems.Add(, , a)
itm.SubItems(1) = b
itm.SubItems(2) = C
' should I place code here and what code?
Loop


Close #1
' or here maybe a For loop to loop through though I am not
' sure just how to write the loop
' I was thinking something like
ItemCount = lv.Listitems.Count
For i = 1 to ItemCount
' some code here not sure what
Next i
End Sub


Need Help on this one

vbMarkO
Title: Display Total amount from DataGrid in asp.net with vb.net   
Name: baljeet kaur
Date: 2010-06-11 3:30:57 AM
Comment:
hi,
I am using DataGridView and I want to display Total Amount by summing the data from one column(from DataGridView).

For Example:
In DataGriView there will be 3 columns, viz, Quantity, Rate and Amount.
Amount will be Quantity * Rate.
At the end I want to display Total Amount, i.e add all the rows in Amount column and display in footer.
If you know anything regarding this plz let me know.

baljeet kaur
baljeetk74@gmail.com
Title: Databind() in VB.net   
Name: Ravi Inder Singh
Date: 2010-05-15 3:45:41 AM
Comment:
Databind() doesn't works in VB.net. Instead DataGridView.DataBindings(___) requires an argument which I don't know what to give....plz help.
Title: So Nice of You   
Name: Kaleem Ullah Khan
Date: 2009-07-15 6:54:21 AM
Comment:
So Sweet Thanks
Title: Thanks Rajiv   
Name: Satish
Date: 2009-02-21 12:37:07 PM
Comment:
Awesome
Title: Reply To Diana   
Name: Rajeev
Date: 2008-12-13 10:43:25 AM
Comment:
Diana,

I am not sure how you insert the data.

In this case, I think, you have to take the first approach.

Thanks,
Rajeev
Title: get the total of value from Data Grid   
Name: Diana
Date: 2008-12-10 9:58:34 PM
Comment:
Hi,

Need your help..
I have 30 value that I insert into data grid.
Then, I want get the total automatically.
Can you help me?
Title: display the total of every colum in hierargird   
Name: xyz
Date: 2008-06-20 11:35:29 AM
Comment:
how to add a total
Title: Display Total amount from DataGridView   
Name: Chris
Date: 2008-02-10 6:08:25 PM
Comment:
Great Job. To the point and very clever...
Title: Display total amt from gridview   
Name: Rajni
Date: 2008-01-23 4:55:13 AM
Comment:
Thanks a lot. Really a very good Artical...Good Work
Title: Display Total amount from DataGridView   
Name: Simran
Date: 2007-11-29 2:37:13 AM
Comment:
Hi,
I am using DataGridView and I want to display Total Amount by summing the data from one column(from DataGridView).

For Example:
In DataGriView there will be 3 columns, viz, Quantity, Rate and Amount.
Amount will be Quantity * Rate.
At the end I want to display Total Amount, i.e add all the rows in Amount column and display in footer.
If you know anything regarding this plz let me know.

Simran
jha_simran@yahoo.com
Title: Thank you very much   
Name: Roman
Date: 2007-11-28 5:26:28 AM
Comment:
Thacks, it works very good on a Pocket PC, with a big number of rows and columns
Title: gridview footer total by using javascript   
Name: rrr
Date: 2007-11-27 3:41:04 AM
Comment:
gridview footer total by using javascript
Title: Calculation in Datagrid   
Name: Shilabhadra Sahu
Date: 2007-09-27 2:59:59 AM
Comment:
Adding data of a column and showing at footer , the code above written not successfull. If u hv ant solution plz mail me on shilabhadra_isti@yahoo.co.in
Title: Good Article   
Name: umesh prajapati - tatvasoft
Date: 2007-08-24 7:37:48 AM
Comment:
Really good article
Title: Very Good   
Name: K.Muralidharan
Date: 2007-06-16 11:58:27 AM
Comment:
This Artical is very useful it helped me very lot.
Title: sumTotal in Footer is possible   
Name: Praveen Paulose
Date: 2007-04-04 4:29:31 AM
Comment:
You can use the below code before calling the DataBind() method.

myDataGrid.DataSource = ds
sumTotal = ds.Tables(0).Compute("SUM(amt_txn)", String.Empty).ToString
myDataGrid.Columns(2).FooterText = "TOTAL: "
myDataGrid.Columns(3).FooterText = sumTotal
myDataGrid.DataBind()
Title: better work   
Name: Mahbub Ali Azad
Date: 2007-03-21 9:51:43 AM
Comment:
This article is really helpful
Title: good work   
Name: Ravinder Singh
Date: 2006-12-29 5:40:54 AM
Comment:
Thanks , it is a nice article. I was looking for this for very long time. But I have a problem , Can we have Subtotals for different rows and then finally grand total of all rows.
With thanks
Title: Super   
Name: Sri
Date: 2006-12-20 10:38:52 AM
Comment:
Artical Helped me alot. :)
Title: Excellent but can u extend it   
Name: Parag
Date: 2006-12-14 1:55:10 AM
Comment:
HI its really a helpfull article but can u extend it.
My condition is

Total_Amount = Convert.ToInt32(ds.Tables[0].Compute("SUM(Amount)",String.Empty));

but can i do like this,
Total_Amount = Convert.ToInt32(ds.Tables[0].Compute("SUM(Amount)if"+ DDL_Credit_or_Debit.selecteditem.text!="DR",String.Empty));
So it will return sum of those collumns where credit is selectd.
Title: Sum Total in DataGrid   
Name: Regan Wilders
Date: 2006-12-06 12:47:52 PM
Comment:
Great suggestion, quick and efficient!
Thank you!
Title: Question   
Name: Meena
Date: 2006-12-05 5:34:10 PM
Comment:
Nice Article. Works great in an web form. How can I modify this code to plug it in VB.NET windows.forms.Datagrid to compute sum of one column on the fly?
Any advice is appreciated.

samyukutti@yahoo.com
Title: Good One   
Name: Sreehari
Date: 2006-11-10 4:31:19 AM
Comment:
Simple & Superb, the article serves the purpose
Title: Its Really Good Article   
Name: Sanjeev
Date: 2006-10-30 6:12:30 AM
Comment:
Its too good to add all fields of a column name...
Title: Excellent Stuffs.. Thx a lot   
Name: Janesh Hari
Date: 2006-10-26 4:42:50 AM
Comment:
this is wonderful... i done it before by writing lenthy codes...

but its just a 2 stmts..


gud,,, thx very much..
Title: .Net Developer   
Name: AE
Date: 2006-10-20 3:57:12 AM
Comment:
sumTotal = ds.Tables(0).Compute("SUM(amt_txn)", String.Empty).ToString

It rocks! Superb...
Title: multiple page   
Name: arjay
Date: 2006-07-10 5:32:50 AM
Comment:
problem occurs when you have a multiple page datagrid... it only computes the total for the first page not the entire grid
Title: Nice   
Name: Bipul
Date: 2006-05-22 7:01:05 AM
Comment:
Have done a great job
Title: Mr   
Name: Pravin
Date: 2006-02-08 12:24:37 AM
Comment:
Excellent article. The compute method is very powerful and can be used inplace of rowfilter.
Title: Porgrammer   
Name: somre
Date: 2006-02-02 3:33:27 PM
Comment:
Excellent article. Made my job much easier. Esp the compute sum part of it
Title: accurate   
Name: vivek singh
Date: 2006-01-13 4:50:24 AM
Comment:
This article is really helpful. And accurate as well
Title: Great   
Name: Anu
Date: 2005-12-21 4:04:48 AM
Comment:
I was struggling for something like this.This helped me
Title: it helped   
Name: Vicury
Date: 2005-11-28 8:05:25 PM
Comment:
it helped. thanks.
Title: Mr.   
Name: Sainey and Ms. Suzy
Date: 2005-11-16 12:12:48 PM
Comment:
Thanks. Works great. Simple and to the point.
Title: Thanks   
Name: Manoj
Date: 2005-11-09 7:39:43 AM
Comment:
Thanks for the article..
Title: Out of the box   
Name: Turlapati pranay
Date: 2005-08-18 2:24:18 AM
Comment:
The article deserves applause and praise for having explained the specifics of some of the most important functionalities of the datagrid

awesome!!!!
Title: transfering datagrid items into table   
Name: farah
Date: 2005-06-21 12:49:28 AM
Comment:
i really like the approach and methid u explain i have inserted items into datagrid now when i click submit button i want to transfer all the item s of datagrid into a new table which has 2more additional cols and delete all the rows of previous table
Dim counter As Integer
Dim TXTREGNO As TextBox
Dim dtg As DataGridItem
Dim reader As OracleDataReader
For counter = 0 To counter = (dispatchgrid.Items.Count - 1)
dtg = dispatchgrid.Items(counter)
If (dtg.ItemType = ListItemType.Item Or dtg.ItemType = ListItemType.AlternatingItem) Then
'Dim cn As OracleConnection = New OracleConnection(ConfigurationSettings.AppSettings("franchisecon"))
' cn.Open()
cmd = New OracleCommand
TXTREGNO = dtg.FindControl("dgtxtregno")
Dim sql As String
sql = ("select query typed here")
Dim adp1 As New OracleDataAdapter
adp1 = New OracleDataAdapter(sql, cn)
Dim ds1 As New DataSet
ds1 = New DataSet
adp1.Fill(ds1, "fdamage")
Dim row As DataRow
For Each row In ds1.Tables("fdamage").Rows()
lblfranchise.Text = row(0).ToString()
lbldamage.Text = row(1).ToString()
lblrplate.Text = row(2).ToString()
lblfplate.Text = row(3).ToString()
lbltplate.Text = row(4).ToString()
lblemboss.Text = row(5).ToString()
lblsnaplock.Text = row(6).ToString()
lblappno.Text = row(7).ToString()
lbldate.Text = row(8).ToString()
Next
adp1.Dispose()
ds1.Dispose()
Dim cmd1 As New OracleCommand
cmd1.Connection = cn
'Dim dat As String
'dat =
Title: Mr   
Name: Israel
Date: 2005-05-04 4:46:55 AM
Comment:
It simple to understand. Great. Can you please provide a source code for calculating "Average" instead of total using ItemDataBound event of the DataGrid control?

Thanks,
Israel
i.o.adetunji@lboro.ac.uk
Title: problem...   
Name: diego
Date: 2005-04-26 12:13:34 PM
Comment:
hey, it works fine with a datatable, but it doesn't work in a dataview... i had to create a dataset filtering the rows in the datatable using the row filter of the dataview:

Dim ds As New DataSet
ds.Merge(CType(dg.DataSource, DataView).Table.Select(CType(dg.DataSource, DataView).RowFilter))

Dim dt As DataTable = ds.Tables(0)

text = CDbl(dt.Compute("Sum([" & dt.Columns(i).ColumnName & "])", String.Empty))(i).ColumnName & "])", String.Empty))
Title: goood   
Name: Vivek
Date: 2005-03-03 5:52:08 AM
Comment:
goood nice
Title: Raj   
Name: Raj
Date: 2005-02-28 1:19:49 PM
Comment:
I tried as per this article and the page blows up.

It was a waste of time and effort.
Title: That's Great!   
Name: Subrahmanyam
Date: 2005-02-23 12:36:41 AM
Comment:
From many a days i was trying the same with DataGrid.

I felt much happy for the article. And also, i am clear in handling the DataGrid events.

Expecting more like from you.

Thanks a lot.
Title: expression column   
Name: sasi
Date: 2005-02-08 5:04:52 AM
Comment:
Excellent .
i have one doubt can i compute a formula like below using expression
(117.29+(8.237*(SQRT(MTOW*1000-5700))))
Title: Good Article   
Name: Apurva Kaushal
Date: 2005-01-31 1:44:23 AM
Comment:
It was really a very good article.
Title: Good piece of code   
Name: Marc Vermeersch
Date: 2004-12-08 3:39:01 AM
Comment:
Good piece of code for making the totals of a datagrid tested in a datagrid in vb.net, its working perfect.
action-data Belgium
Title: Mr   
Name: Raghavendra
Date: 2004-10-28 8:38:51 AM
Comment:
Good article. Done a great Job. Keep it up.
Title: Good Work.   
Name: C.DHAKCHINA MOORTHY
Date: 2004-10-09 7:33:50 AM
Comment:
This is really a good article. Thanks a lot.
Title: Good Article   
Name: Murali
Date: 2004-10-08 2:43:03 AM
Comment:
Hi,
This isvery Good article isit possible to add morethan one footer in datagrid send reply thru muraedu@yahoo.co.in
Title: Great   
Name: Mr Chan
Date: 2004-10-05 8:31:04 PM
Comment:
Great
Title: Good   
Name: Samir
Date: 2004-10-05 1:34:18 AM
Comment:
Samll but uerful functionlity, thanx
Title: How to implement this in C# ? Can't find ItemDataBound Event !!   
Name: Tushar
Date: 2004-10-03 1:03:41 PM
Comment:
Hii
I searched the documentation also and used WinCV also.
But i am unable to find this event. Is it necessary to first bind this to a datasourse or we can use this event without binding the grid to the source.
Please do reply me at tushar_ag@yahoo.com

Thanks
Title: asp.net   
Name: wissam
Date: 2004-10-01 11:35:53 AM
Comment:
Hi, how are you
please I want code source of ASP.net
thank you ,bye
Title: gr8 article   
Name: Chetan
Date: 2004-10-01 9:05:36 AM
Comment:
I was trying such kind of thing .....ur article gave me proper guidence....will u please tell how to add controls to datagrid???
Title: maintain footer total on postback?   
Name: red
Date: 2004-09-30 9:15:16 AM
Comment:
But doesn't this total get lost on POSTBACK? How do you maintain state on the footer when the page is posted back and no databind occurs?
Title: Summary Totals   
Name: Denis Cilliers
Date: 2004-09-30 8:17:33 AM
Comment:
You can build the summary totals by processing the return dataset from the database.

Of course this would entail building a second dataset for the datagrid. but it is a method of intercepting the data, manipulating it and then displaying it.

You might want to format data in the dll rather than in SQL.
Title: Minor detail, but it's still software...   
Name: Hans Dingemans
Date: 2004-09-29 3:14:16 PM
Comment:
Typo in first part of code (for the rest, Good article!)

PROTECTED Sub myDataGrid_ItemDataBound (ByVal sender As System.Object,
ByVal e As DataGridItemEventArgs) Handles myDataGrid.ItemDataBound
Title: Nice work!   
Name: BradleyT
Date: 2004-09-29 3:00:27 PM
Comment:
To the person who said you could use the db function in your SQL - not everyone uses a SQL db as a datasource! Ever heard of XML?

And to the person who wanted sub totals - there's a nice article on 4guysfromrolla.com
Title: Good Article   
Name: Jitendra Patil
Date: 2004-09-29 2:28:45 PM
Comment:
Good article...keep it up!
Title: Good article   
Name: Vicky
Date: 2004-09-29 11:46:37 AM
Comment:
Displaying Grand Total is Ok. Tough would be to display Summary totals for the group. Could you give us some idea about that.

Thanks in advance
Regards
vicky
Title: Wow!!! Can I have more such kind of Info   
Name: Ashish Bali
Date: 2004-09-29 3:16:38 AM
Comment:
An excellent article.Keep up Good Work.
Ps: can u show up with some more articles like this
Title: also   
Name: Chris
Date: 2004-07-26 5:16:48 PM
Comment:
You could also use database functionality (such as "with rollup" on a "group by" in SQL Server) to calculate the sum row and avoid use a dataset.
Title: Awesome   
Name: Kutta Kamina
Date: 2004-07-22 2:40:19 PM
Comment:
This is awesome
Title: Excellent and Concise   
Name: Paul Laberge
Date: 2004-07-22 2:35:02 PM
Comment:
This is an excellent article on one of the most important constructs within .net; it features two interesting approaches to implementing a DataGrid and discusses the pros and cons of each. Well done; no frivolous text, just the facts!
Title: Mr   
Name: Siju
Date: 2004-07-22 2:23:26 PM
Comment:
You are great
Title: Excellent Article   
Name: Ashish
Date: 2004-07-22 2:06:40 PM
Comment:
I had been looking for something similar to implement for my website. Thanks for the article.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-19 5:21:35 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search