Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Datatable group by with linq in vb.net

I'm trying to get aggregate values from data table. But can't figure out how. Saw some examples in c#, but couldn't translate those to vb.net.

I have data table

Month, campaign, sales, leads, gross
1           1                5         10       1000
1            2               0          5         0
2            1               2          0         300
2            2               1          3         200

I need to get a result :

Month, sales, leads, gross
1           5        15       1000
2           3         3         500

I don't want to loop and combine values manually. Please help

like image 529
Nick Avatar asked Dec 21 '12 22:12

Nick


1 Answers

You want to Group by Month? You can use Sum to sum the groups:

Dim query = From row In dt
        Group row By Month = row.Field(Of Int32)("Month") Into MonthGroup = Group
        Select New With {
            Key Month,
            .Sales = MonthGroup.Sum(Function(r) r.Field(Of Int32)("Sales")),
            .Leads = MonthGroup.Sum(Function(r) r.Field(Of Int32)("Leads")),
            .Gross = MonthGroup.Sum(Function(r) r.Field(Of Int32)("Gross"))
       }

For Each x In query
    Console.WriteLine("Month:{0} {1} {2} {3}", x.Month, x.Sales, x.Leads, x.Gross)
Next

This is a mixture of Linq query- and method-syntax.

like image 56
Tim Schmelter Avatar answered Oct 07 '22 13:10

Tim Schmelter