# Sum each unique Products

Page 1 of 1

## 6 Replies - 305 Views - Last Post: 28 October 2019 - 08:57 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'https://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=417666&amp;s=2cbf3607563f75331beea79a178c9e19&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 Interplain

Reputation: 0
• Posts: 48
• Joined: 02-November 16

# Sum each unique Products

Posted 25 October 2019 - 06:02 AM

Hello, I have below code for counting each individual Product material from column 3, how would i add so that it would sum up the total from another column for each of the products in Column 3

Currently

Number of unique Products
Column 3
Potatoes Bags 30
Carrots Bags 20

to give:

Number of unique Products
Column 3             Column 7
Potatoes Bags 30      750kg
Carrots Bags 20       500kg

Dim dic As Dictionary(Of String, Integer) = New Dictionary(Of String, Integer)()
Dim cellValue As String = Nothing

For i As Integer = 0 To DataGridView1.Rows.Count - 1

If Not DataGridView1.Rows(i).IsNewRow Then
cellValue = DataGridView1(3, i).Value.ToString()

If Not dic.ContainsKey(cellValue) Then
Else
dic(cellValue) += 1
End If
End If
Next

Dim sb As StringBuilder = New StringBuilder()
sb.AppendLine("Number of unique Products:")

For Each keyvalue As KeyValuePair(Of String, Integer) In dic
sb.AppendLine(String.Format("{0}:  Bags {1}", keyvalue.Key, keyvalue.Value))
Next

LabelUniqueProduct.Text = sb.ToString()
LabelProducts.Text = dic.Count.ToString()

I know how to do the sum on whole column but no to include in the above string, would be grateful for some guidance please.

Thank you

Is This A Good Question/Topic? 0

## Replies To: Sum each unique Products

### #2 Interplain

Reputation: 0
• Posts: 48
• Joined: 02-November 16

## Re: Sum each unique Products

Posted 25 October 2019 - 12:13 PM

I have figured how to add the Weight but not for Individual Items. I would really appreciate some guidance please.

Public Sub TotalProduct()

Dim dic As Dictionary(Of String, Integer) = New Dictionary(Of String, Integer)()
Dim cellValue As String = Nothing
Dim Sum As Double = Nothing
Dim Dec As Double = Nothing

Sum = Math.Round(Sum)

For i As Integer = 0 To DataGridView1.Rows.Count - 1

Double.TryParse(Me.DataGridView1(5, i).FormattedValue.ToString(), Dec)
Sum += dec
Next

For i As Integer = 0 To DataGridView1.Rows.Count - 1

If Not DataGridView1.Rows(i).IsNewRow Then
cellValue = DataGridView1(3, i).Value.ToString()

If Not dic.ContainsKey(cellValue) Then

Else
dic(cellValue) += 1

End If
End If
Next

Dim sb As StringBuilder = New StringBuilder()
sb.AppendLine("Stock Count Available:")

For Each keyvalue As KeyValuePair(Of String, Integer) In dic
sb.AppendLine(String.Format("{0}:  Bags {1}   Total Weight: {2:n0}", keyvalue.Key, keyvalue.Value, Sum))
Next

LabelUniqueProduct.Text = sb.ToString()
LabelAmountProducts.Text = dic.Count.ToString()

End Sub

### #3 Interplain

Reputation: 0
• Posts: 48
• Joined: 02-November 16

## Re: Sum each unique Products

Posted 28 October 2019 - 05:18 AM

How can i get it to Total up each individual Products weight. It shows the the total of Bags but not the weight for each product. If two products exist in the datagridview the code give each product plust the amount of bags for each but not the weight. the weights are the same.

Try

Dim CountByProductName As Dictionary(Of String, Integer) = New Dictionary(Of String, Integer)()
Dim AmountByProductName As Dictionary(Of String, Double) = New Dictionary(Of String, Double)()
Dim ProductName As String = Nothing

Dim Amount As Double = 0

For i As Integer = 0 To DataGridView1.Rows.Count - 1

If Not DataGridView1.Rows(i).IsNewRow Then
ProductName = DataGridView1(3, i).Value.ToString()
Amount = CDbl(Me.DataGridView1(5, i).Value)

If Not CountByProductName.ContainsKey(ProductName) Then
Else
CountByProductName(ProductName) += 1
AmountByProductName(ProductName) += Amount
End If
End If
Next

Dim sb As StringBuilder = New StringBuilder()
sb.AppendLine("Stock Count Available")

For Each keyvalue As KeyValuePair(Of String, Integer) In CountByProductName
sb.AppendLine(String.Format("{0}:  Bags: {1}   Total Weight: {2:n0}", keyvalue.Key, keyvalue.Value, Amount))
Next

LabelUniqueProduct.Text = sb.ToString()
LabelAmountProducts.Text = CountByProductName.Count.ToString() & "   " & "Item(s)"

Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

### #4 modi123_1

• Suitor #2

Reputation: 15427
• Posts: 61,808
• Joined: 12-June 08

## Re: Sum each unique Products

Posted 28 October 2019 - 07:16 AM

Just getting to this.. I am not following. Step back and explain what your data looks like and what you are attempting to make it look like? This may be a sql query fix.

### #5 Interplain

Reputation: 0
• Posts: 48
• Joined: 02-November 16

## Re: Sum each unique Products

Posted 28 October 2019 - 08:19 AM

I have a DataGridView that is filtered and populated with products. like below:

Product         Bag number    Weight
Apples           1             25
Apples           2             25
Apples           3             25
Potatoes         1             35
Potatoes         2             35
Potatoes         3             35
Potatoes         4             35
Potatoes         5             35
Onions           1             15
Onions           2             15

Looking for a way to filter them to a label with Dictionary

Want it To look like
Product       Bags      Weight
Apples         3         75
Potatoes       5         175
Onions         2         30

I have the code working to some level where it will find individual Product and Bags but the Weight is the whole Weight Amount of the last row. I want it to find the distinct products and give the amount of bags for each and the weight of distinct weights please.

This post has been edited by modi123_1: 28 October 2019 - 08:29 AM

### #6 modi123_1

• Suitor #2

Reputation: 15427
• Posts: 61,808
• Joined: 12-June 08

## Re: Sum each unique Products

Posted 28 October 2019 - 08:35 AM

Okay.. so you would want to tackle that with a sql query. The data grid view is okay for displaying stuff, but SQL would be better to do this at the source. LINQ would work as well to some degree.

Ex:

cre ate tabl e #foo(pro varc har(20), bag int, weight int)

ins ert into  #foo(pro, bag, weight) values ('Apples', 1, 25)
ins ert into  #foo(pro, bag, weight) values ('Apples', 2, 25)
ins ert into  #foo(pro, bag, weight) values ('Apples', 3, 25)
ins ert into  #foo(pro, bag, weight) values ('Potatoes', 1, 35)
ins ert into  #foo(pro, bag, weight) values ('Potatoes', 2, 35)
ins ert into  #foo(pro, bag, weight) values ('Potatoes', 3, 35)
in sert into  #foo(pro, bag, weight) values ('Potatoes', 4, 35)
ins ert into  #foo(pro, bag, weight) values ('Onions', 1, 15)
ins ert into  #foo(pro, bag, weight) values ('Onions', 2, 15)

sel ect *
from #foo

se lect pro, count(bag) as bag_coutn, sum(weight) as weight_sum
from #foo
gr oup by pro

dr op table #foo

pro                  bag         weight
-------------------- ----------- -----------
Apples               1           25
Apples               2           25
Apples               3           25
Potatoes             1           35
Potatoes             2           35
Potatoes             3           35
Potatoes             4           35
Onions               1           15
Onions               2           15

(9 rows affected)

pro                  bag_coutn   weight_sum
-------------------- ----------- -----------
Apples               3           75
Onions               2           30
Potatoes             4           140

### #7 modi123_1

• Suitor #2

Reputation: 15427
• Posts: 61,808
• Joined: 12-June 08

## Re: Sum each unique Products

Posted 28 October 2019 - 08:57 AM

https://docs.microso...group-by-clause

LINQ way because it's been sometime for me..

Dim ii As New List(Of nard)
ii.Add(New nard With {.pro = "Apples", .bag = 1, .weight = 25})
ii.Add(New nard With {.pro = "Apples", .bag = 2, .weight = 25})
ii.Add(New nard With {.pro = "Apples", .bag = 3, .weight = 25})
ii.Add(New nard With {.pro = "Potatoes", .bag = 1, .weight = 35})
ii.Add(New nard With {.pro = "Potatoes", .bag = 2, .weight = 35})
ii.Add(New nard With {.pro = "Potatoes", .bag = 3, .weight = 35})
ii.Add(New nard With {.pro = "Potatoes", .bag = 4, .weight = 35})
ii.Add(New nard With {.pro = "Onions", .bag = 1, .weight = 15})
ii.Add(New nard With {.pro = "Onions", .bag = 2, .weight = 15})

Dim pp = From x In ii
Group x By x.pro
Into x2 = Group, Count(), Sum(x.weight)

For Each item As Object In pp
Console.WriteLine(\$"{item.pro}, {item.Count}, {item.Sum}")
Next

Apples, 3, 75
Potatoes, 4, 140
Onions, 2, 30