[RESOLVED] DataGridView sum columns with condition

[ivansmo]

Hello to all, this is the closest example to my problem, https://www.vbforums.com/showthread....d-on-condition but with a big difference. I need to pass through the whole DGV and get SUM(amount) where Ref.No AND Code are the same (repeating).



These values are populated from XML files, and my project has no database. Is there any well-known logic behind this?

Something like

Go through DGV and SUM(amount) where Ref.No AND Code repeats.

Output should be like:

38278, 001, 0
38278, 032, 10242.65
38278, 023, 4921.05
38278, 024, 8201.74
38278, 090, 9826.54

[Zvoni]

Use LINQ
https://stackoverflow.com/questions/...le-or-dataview

EDIT: Alternative: SQLite-InMemory-Database.
Throw your rows into that InMem-DB, do the SQL, retrieve the results, be done with it

[ivansmo]

@Zvoni thank you for helping me. Please, tell me how to make a table connection with my DGV? This is the code you give me a link to. If you could make it worthy?

 Dim table As New DataTable
        Dim qur = table.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("Number"))

        Dim fruitGroups = table.AsEnumerable().GroupBy(Function(row) New With {
        Key .Number = row.Field(Of String)("Number"),
        Key .Code = row.Field(Of String)("Code")
            })

        Dim tableResult = table.Clone()
        For Each grp In fruitGroups
            tableResult.Rows.Add(grp.Key.Number, grp.Sum(Function(row) row.Field(Of Decimal)("Amount")), grp.Key.Code)
        Next

[Zvoni]

Sorry, no idea, since i'm not a NET-er.
It's just what i found.

Wait for the others

at a guess:

Dim MyGroup = table.AsEnumerable().GroupBy(Function(row) New With {
        Key .RefNo = row.Field(Of String)("Ref.No"),  'No Idea if it's "Of String"
        Key .Code = row.Field(Of String)("Code")
    })

Dim tableResult = table.Clone()
For Each grp In MyGroup
    tableResult.Rows.Add(grp.Key.RefNo, grp.Sum(Function(row) row.Field(Of Double)("Amount")), grp.Key.Code)
Next

But as i said: No idea

[ivansmo]

So this is how I populate DataGridView:

     Dim xe = XElement.Load(filePath)
            Dim itemNO As Integer = 0
            Dim code As String
            Dim base As String
            Dim rate As String
            Dim amount As String
            '   Dim attdocuments As String
            For Each itm As XElement In xe.<Item>.<Taxation>.<Taxation_line>.Where(Function(el) el.<Duty_tax_code>.FirstOrDefault IsNot Nothing AndAlso el.<Duty_tax_code>.FirstOrDefault.Value IsNot <null/>)
                code = itm...<Duty_tax_code>.ToList.Value
                base = itm...<Duty_tax_Base>.ToList.Value
                rate = itm...<Duty_tax_rate>.ToList.Value
                amount = itm...<Duty_tax_amount>.ToList.Value
                '      attdocuments = itm...<Attached_doc_item>.FirstOrDefault.Value
                On Error Resume Next
                'Stop
                If base <> vbNullString Then
                    itemNO += 1

                    DGV_data.Rows.Add(Date_1, RefNo, CIoffice, Items, Total, itemNO, code, base, rate, CDec(Val(amount)))
                Else
                End If
            Next
        Next
        DGV_data.AllowUserToAddRows = False

That part works great, and I only expect decimal values inside the Amount column. Does anyone have an idea?

[ivansmo]

@Zvoni Hi, I've decided to go with a local database. I have a Table with the same column names. If you could help me with SQL query that would be great. So I need to join data with 2 conditions and make SUM(Amount) of repeating data in two columns.

Thank you.

[Zvoni]

Select refno, code, sum(amount) as sumamount from sometable group by refno, code order by refno, code

[ivansmo]

Thanks man. It works great

SELECT 
    refno, 
    code, 
    SUM(amount) AS sumamount
FROM 
    Table1
GROUP BY 
    refno, 
    code
ORDER BY 
    refno, 
    code;