Get and count

[Cari]

Hi. I trying to get the count of occurrences.
Table:
Tom , Blue
Sally , Blue
Sally , Blue
Sally , Blue
Jerry , Blue
Sally , Red
Sally , Red
Tom , Yellow
Tom , Yellow
Sally , Yellow

Count the colour based on the person. at the same time I only want to count the top 2 person that have the most colors.

Result: * Jerry don’t need cause I only want Top 2 person.
Sally - Red 2
Sally - Blue 3
Sally - Yellow 1
Tom - Blue 1
Tom - Yellow 2

Thank you!!

[Yoichi]

Hi,

Can you share your expected output?

Regards,

[Cari]

Sorry I edited the question. Accidentally post without finish writing.

[Yoichi]

Hi,

Alright. Hope the following helps you.

dtResult = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r(0).ToString,r(1).ToString)).Select(Function(g) dtResult.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Count()},False)).CopyToDataTable()

Sample20220107-1.zip (2.9 KB)

The above returns result including Jerry. If you need to filter out Jerry record, can you share its condition (What is “Top 2 person”) ?

Regards,

[Cari]

Sally have 6
Tom have 3
Jelly have 1
Hence, I only want top 2 persons that has the most count in total which is Sally and Tom.

[Yoichi]

Hi,

Can you try the following?

dtResult = dt.AsEnumerable.GroupBy(Function(r) r(0).ToString).OrderByDescending(Function(g) g.Count).Take(2).SelectMany(Function(g) g).GroupBy(Function(r) Tuple.Create(r(0).ToString,r(1).ToString)).Select(Function(g) dtResult.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Count()},False)).CopyToDataTable()

Sample20220107-1v2.zip (4.8 KB)

Regards,

[Cari]

Yes! This is what I want! Thanks a lot!