Click here to Skip to main content
15,302,655 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi guys
I am trying to figure out how to calculate totals from unique values in a datagridview.
It will be easier to give you this example:



Unbound DGV1				
				
Client ID	Value	etc	etc	etc
234	        400.34			
234	        500.1			
222	        200.44			
234	        620.34			
111	        130.34			
234	        450.99			
111	        234.56			
222	        500.45			
				
Output like this on DGV2				
				
Client ID	Value	etc	etc	etc
111	        364.9			
222	        700.89			
234	        1971.77			
				
or output like / on same DGV1				
				
Client ID	Value	etc	etc	etc
111	        130.34			
111	        234.56			
Total           364.9			
				
222	        200.44			
222	        500.45			
Total           700.89			
				
234	        400.34			
234	        500.1			
234	        620.34			
234	        450.99			
Total           1971.77			

if this makes any sense what i'm trying to achieve.
Thanks in advance for the help

thx MJ
Posted
Updated 1-Jun-15 6:25am
v2
Comments
Maciej Los 1-Jun-15 14:06pm
   
If the DataGridView.DataSource is DataTable, you can use Linq to compare DataGridView.Rows...
sudevsu 1-Jun-15 15:44pm
   
also you can use Dataview to filter what ever value you want.

1 solution

Assuming that DataGridView.DataSource[^] is DataTable[^], you can use Linq to group data by ClientID.

Sample code:
VB
Dim dt As DataTable = New DataTable()
Dim dc As DataColumn = New DataColumn("Client ID", Type.GetType("System.Int32"))
dt.Columns.Add(dc)
dc = New DataColumn("Value", Type.GetType("System.Double"))
dt.Columns.Add(dc)
dt.Rows.Add(New Object(){234, 400.34})
dt.Rows.Add(New Object(){234, 500.1})
dt.Rows.Add(New Object(){222, 200.44})
dt.Rows.Add(New Object(){234, 620.34})
dt.Rows.Add(New Object(){111, 130.34})
dt.Rows.Add(New Object(){234, 450.99})
dt.Rows.Add(New Object(){111, 234.56})
dt.Rows.Add(New Object(){222, 500.45})


Dim groupedRows = dt.AsEnumerable().GroupBy(Function(a) a.Field(Of Integer)("Client ID")).[Select](Function(g) New With { _
    Key .ClientID = g.Key(), _
    Key .Value = g.Sum(Function(a) a.Field(Of Double)("Value")) _
}).ToList()


Now, you can use this variable as an DataSource:
VB
DataGridView2.DataSource = groupedRows


groupedRows return something like:
ClientID Value
234      1971.77 
222       700.89 
111       364.9


For further information, please see: LINQ - Sample Queries[^]

Good luck!

[EDIT]
Working sample[^]
   
v2
Comments
Mario Van Deventer 2-Jun-15 10:28am
   
Hi Maciej

Do i need Linq to run this code
It returns nothing on DGV 2

Is Linq FREE or how does it work
I downloaded the Linqpad but that did not help.

any suggestions?
Maciej Los 2-Jun-15 11:21am
   
Solution provided by me uses Linq. Yes, linq is free. Its part of .net framework starting from 3. version.
Mario Van Deventer 4-Jun-15 11:16am
   
Hi Maciej
Thank you for the help.
The above code you gave me..
It should work? right
Im just wondering if im doing something wrong
because it returns nothing on Datagridview2

i have been taking time reading up on LINQ .. pretty cool
but not sure where to start.
getting back to the code you sent me: have you tested it?
i cant get it to work.
please help .. :)
If im being a pain just tell me to find a solution... LOL

thanks a stack
Maciej Los 7-Jun-15 8:16am
   
Sorry, i was bit busy. Please, remaind me tommorow. I promise to post a link to "working example". OK?
Mario Van Deventer 8-Jun-15 9:20am
   
Hi Maciej

you are a star thank you :)
Maciej Los 8-Jun-15 10:20am
   
Link to source file has been added. ;)
Mario Van Deventer 8-Jun-15 12:27pm
   
AWESOME
I work on visual studio 2010 but managed to copy the module and form code to my app.
modified it a bit and its perfect.

thank you so much for all the help.

Keep well
Mario
Maciej Los 8-Jun-15 13:56pm
   
You're very welcome ;)
Mario Van Deventer 14-Jun-15 3:40am
   
Hi Macie
the code is working very well and i had to DO a "MACGUYVER" to get it to work
My DGV is unbound and it only works with Databound DGV.
So i saved the unbound to temp textfile and then bound a new DGV to the textfile
now it works..

But it wont read the value as DOUBLE so i had to use STRING.
Now it only returns the STRING and not the decimal.
I even saved the initial values as double but no luck.

your code : Key .Value = g.Sum(Function(a) a.Field(Of Double)("Value")) _

my Code: Key .Value = g.Sum(Function(a) a.Field(Of String)("Value")) _

returns: 450
should return: 449.50

Any suggestions ?
Maciej Los 14-Jun-15 14:59pm
   
Sorry, i have no idea...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900