Group By and Count with several conditions

Hi there,
How can I perform group by and count the return records in a datatable according to the condition below using linq query?

dt.[Column1] = dt.[Column4] AND dt.[Column2] = dt.[Column5] AND dt.[Column3] = dt.[Column6]

hi @ppr is this something you could help with?

can you paste some sample data. Thanks

SampleGroupBy.xlsx (10.0 KB)
Attached the sample data and expected output. As you can see Paycheck Data is not in the correct format, I need to format it as well.

Requirement group and count ID by Paycheck Date = Payment Date, Start Date = Period Start Date, End Date = Period End Date of the same row

give a try on following

prepare the target datatable with build datatable activity - dtResult
Columns: Group, Count

use an assign activity
left side: dtResult
right side:

(From d in dtData.AsEnumerable
Group d by k=d("ID").toString.Trim into grp=Group
let grs = (From g in grp
               Where CDate(g(1).toString).toString("dd.MM.yyyy").equals(g(4).toString)
               Where g(2).toString.Equals(g(5).toString)
               Where g(3).toString.Equals(g(6).toString)
               Select dr=g).toList
Let ra = new Object(){k,grs.Count}
Select dtResult.Rows.add(ra)).CopyToDataTable

also have a look here:

the non LINQ approach maybe also can help to better handle the different date formatings

thanks a lot. it works, the data is from left join, any recommendation to handle empty date for CDate(g(1).toString).toString(“dd.MM.yyyy”) as it fails if date is empty? something that i could handle in the same query like give a dummy date?

different approaches to handle it

  • filtering out the row if it is a non valid date
  • using a default date for non valid dates

Just let us know what will be the best fit for your case. In case of dummy date which value should it have?

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.