locked
Using Linq extensions to group DataTable by multiple columns with multiple sums RRS feed

  • Question

  • User-2010311731 posted

    Greetings,

    I am having trouble coming up with a Linq expression that sums three columns and groups by three columns in a DataTable.  Here is the source table...

    ReceiptDate  ReceiptNumber  Fund             Tax      Interest  Penalty
    1/1/2014     1              Property Tax     100.00   0.00      0.00
    1/1/2014     1              Property Tax     1000.00  0.00      0.00
    1/1/2014     1              Forest Fire Svc  50.00    0.00      0.00
    1/1/2014     1              Property Tax     1000.00  0.00      0.00
    1/1/2014     1              Property Tax     0.00     20.00     0.00
    1/1/2014     1              Property Tax     0.00     200.00    0.00
    1/1/2014     1              Property Tax     0.00     0.00      30.00
    1/1/2014     1              Property Tax     0.00     0.00      300.00
    1/1/2015     2              Property Tax     100.00   0.00      0.00
    1/1/2015     2              Property Tax     1000.00  0.00      0.00

    And here is the output I am trying to get...

    ReceiptDate  ReceiptNumber  Fund             Tax      Interest  Penalty
    1/1/2014     1              Property Tax     1100.00  220.00    330.00
    1/1/2014     1              Forest Fire Svc  50.00    0.00      0.00
    1/1/2015     2              Property Tax     1100.00  0.00      0.00

    Any help would be greatly appreciated!

    Matt

    Friday, June 26, 2015 6:31 PM

Answers

  • User1577371250 posted

    Hi,

    try this

    var query = (from row in dt.AsEnumerable()
                group row by new 
    {
    ReceiptDate = row.Field<DateTime>("ReceiptDate"),
    ReceiptNumber = row.Field<int>("ReceiptNumber"),
    Fund = row.Field<string>("Fund")
    } into grp select new { ReceiptDate = grp.Key.ReceiptDate, ReceiptNumber = grp.Key.ReceiptNumber,
    Fund = grp.Key.Fund, Tax = grp.Sum(r => r.Field<Decimal>("Tax")),
    Interest = grp.Sum(r => r.Field<Decimal>("Interest")),
    Penalty = grp.Sum(r => r.Field<Decimal>("Penalty")) }).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 27, 2015 8:47 AM

All replies

  • User1577371250 posted

    Hi,

    try this

    var query = (from row in dt.AsEnumerable()
                group row by new 
    {
    ReceiptDate = row.Field<DateTime>("ReceiptDate"),
    ReceiptNumber = row.Field<int>("ReceiptNumber"),
    Fund = row.Field<string>("Fund")
    } into grp select new { ReceiptDate = grp.Key.ReceiptDate, ReceiptNumber = grp.Key.ReceiptNumber,
    Fund = grp.Key.Fund, Tax = grp.Sum(r => r.Field<Decimal>("Tax")),
    Interest = grp.Sum(r => r.Field<Decimal>("Interest")),
    Penalty = grp.Sum(r => r.Field<Decimal>("Penalty")) }).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 27, 2015 8:47 AM
  • User-2010311731 posted

    Works great.  Thank you!

    Monday, June 29, 2015 10:44 AM