Group data table and sum values in different columns

[krissia.salazar.enriquez (KrissiaSaE)]

Hi!
I have a table like this:
Employee | 2020 | 2021 | 2022
1 |268 | 269 | 270
2 |2656| 269 | 22370
2 |2688| 2329 | 2720
3 |2878| 2659 | 20
3 |28 | 2966 | 20

I need to group by employee and sum each year (column), to be reflected in a new DT with totals per column. Can this be done with only official activities?

Thanks!

[muhammedyuzuak]

Hi @krissia.salazar.enriquez,

You need both grouping by employee and the sum of these groups by year, right?

Regards,
MY

[Yoichi]

Hi,

Can you try the following?

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("Employee").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(r) CInt(r("2020").ToString)),g.Sum(Function(r) CInt(r("2021").ToString)),g.Sum(Function(r) CInt(r("2022").ToString))},False)).CopyToDataTable

Sample20220603-1.zip (2.5 KB)

Regards,

[ppr (Peter Preuss)]

@krissia.salazar.enriquez

Can this be done with only official activities?

In general LINQ is a good option to do this. Have a look here where at the begin a Non-LINQ approach for grouping the data is introduced

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[krissia.salazar.enriquez (KrissiaSaE)]

Yes, that’s what I need.

[krissia.salazar.enriquez (KrissiaSaE)]

Thank you! It worked fine for the 92 columns I have to sum. However, I didn’t mention that I also have some other columns in my table I don’t need to sum (text, for example, the employee name) and when I run this it’s not showing that data… can we adjust it to show those columns also?

[Yoichi]

Hi,

In this case, we need to know which data should be kept (first row of the group etc.) and which column should be sum or not. If you can share specific sample for it, we might be able to show a sample expression.

Regards,

[krissia.salazar.enriquez (KrissiaSaE)]

Thanks,
Using the same example, let’s assume we have two more columns I don’t want to sum: Employee location and Employee Name. I don’t need those to group since location may be different, but I don’t care (it can reflect the first value, for example, it can reflect Paris for Marie even though she was in Paris and London…). I need only to use the ID to group, sum by year/employee and keep the other two columns.

[Yoichi]

Hi,

In this case, we need to decide which row is kept in each group (especially Employee location). If the first row is kept, the following expression will work.

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("Employee ID").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.First().Item("Employee location"),g.First().Item("Employee Name"),g.Key,g.Sum(Function(r) CInt(r("2020").ToString)),g.Sum(Function(r) CInt(r("2021").ToString)),g.Sum(Function(r) CInt(r("2022").ToString))},False)).CopyToDataTable

Sample20220607-1.zip (14.7 KB)

Regards,

[krissia.salazar.enriquez (KrissiaSaE)]

Thank you, Yoichi, it worked perfectly!