Sum Aggregate report

[qwerty1]

Hello,

I was looking for some help with reporting data.

Is there a way to modify the data from this:

dtTest:
|Level|Subject|Grade|Resullt|

|Sophomore|English Lit| 45|Fail
|Sophomore|English Lit| 86| Pass
|Sophomore|English Lit| 72| Pass
|Sophomore|Math| 82| Pass
|Sophomore|Math| |79 |Pass
|Sophomore|Math| |0 |Fail
|Jnr|English Lit| 68| Fail
|Jnr|English Lit| 78| Pass
|Jnr|Math| 68| Fail
|Jnr|Math| 20| Fail
|Jnr|Math| 78| Pass

To this?

dtResult:

|Level| Subject | Pass| Fail|
|Sophomore| English Lit |158| 45
|Sophomore|Math| 161 | 0
|Jnr|English Lit| 78| 68
|Jnr|Math| 78| 88

Thank you

[Airoe (NoCodeSherlock)]

Hi, do u have that data in datatable or just in string like that?

[qwerty1]

Hi,

The data is within a datatable. All columns are strings except from Grade which is a int32

Thanks

[ashokkarale]

@qwerty1,

Use this LINQ query

dt1.AsEnumerable() _
      .GroupBy(Function(row) New With { _
          Key .Level = row.Field(Of String)("Level"), _
          Key .Subject = row.Field(Of String)("Subject") _
      }) _
      .Select(Function(Group) New With { _
          .Level = Group.Key.Level, _
          .Subject = Group.Key.Subject, _
          .Pass = Group.Sum(Function(row) If(row.Field(Of String)("Result") = "Pass", row.Field(Of Integer)("Grade"), 0)), _
          .Fail = Group.Sum(Function(row) If(row.Field(Of String)("Result") = "Fail", row.Field(Of Integer)("Grade"), 0)) _
      }) _
      .Select(Function(item) dtOutput.Rows.Add(item.Level, item.Subject, item.Pass, item.Fail)) _
      .CopyToDataTable()

Solution:

Output:

Thanks,
Ashok

[qwerty1]

Great! Thank you. Will try to implement