6 Replies - 265 Views - Last Post: 28 October 2019 - 08:57 AM Rate Topic: -----

#1 Interplain   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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
                    dic.Add(cellValue, 1)
                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   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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
                    dic.Add(cellValue, 1)

                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




Was This Post Helpful? 0
  • +
  • -

#3 Interplain   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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
                        CountByProductName.Add(ProductName, 1)
                        AmountByProductName.Add(ProductName, Amount)
                    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




Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,575
  • 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.
Was This Post Helpful? 0
  • +
  • -

#5 Interplain   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,575
  • 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

Was This Post Helpful? 1
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,575
  • 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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1