Social Icons

Pages

Saturday, May 7, 2016

Apply group by clause on Datatable in Vb.net

       'create data table
        Dim dtExpensesType As New DataTable
        dtExpensesType.Columns.Add("country", GetType(String))
        dtExpensesType.Columns.Add("year", GetType(String))
        dtExpensesType.Columns.Add("act_expenses", GetType(Decimal))
        dtExpensesType.Columns.Add("Bud_expenses", GetType(Decimal))

        dtExpensesType.Rows.Add("Malaysia", "2013", 10000, 15000)
        dtExpensesType.Rows.Add("Malaysia", "2014", 30000, 32000)
        dtExpensesType.Rows.Add("Malaysia", "2015", 90000, 80000)
        dtExpensesType.Rows.Add("Malaysia", "2016", 10000, 11000)

        dtExpensesType.Rows.Add("Singapore", "2013", 20000, 25000)
        dtExpensesType.Rows.Add("Singapore", "2014", 30000, 42000)
        dtExpensesType.Rows.Add("Singapore", "2015", 90000, 30000)
        dtExpensesType.Rows.Add("Singapore", "2016", 20000, 81000)

        dtExpensesType.Rows.Add("China", "2013", 20000, 25000)
        dtExpensesType.Rows.Add("China", "2014", 30000, 42000)
        dtExpensesType.Rows.Add("China", "2015", 90000, 30000)
        dtExpensesType.Rows.Add("China", "2016", 20000, 81000)

                  'groub by country
                   Dim groupByQuery = From row In dtExpensesType.AsEnumerable()
                           Group row By Name = New With {
                        Key .country = row("country")
                        } Into NameGroup = Group
                           Select New With {
                        .country = Name.country,
                        .act_expenses = NameGroup.Sum(Function(r) r("act_expenses")),
                        .Bud_expenses = NameGroup.Sum(Function(r) r("Bud_expenses"))
                    }

                 Dim val
                For Each val In groupByQuery
                Console.WriteLine(val.country & "," & val.act_expenses & "," &  
                val.Bud_expenses)

                Next

1 comment:

  1. Found this new website. Really helpful for the aptitude preparation tests.Must see.!!!
    http://www.kidsfront.com/competitive-exams/quantitative-aptitude-practice-test.html

    ReplyDelete