Count and Group by multiple columns in a foreach statement in C#

If i have understood your question correctly, you need to count and group by list in foreach loop.

Suppose this is your table

A       B
----------
x       g
x       g
x       g
x       s
y       g
y       g

and you need output like below

A       B       COUNT(B)
-------------------------
x       g         3
x       s         1
y       g         2

and MyList is your list , then you can have query like below

var result = MyList.GroupBy(x => new {x.A, x.B})
                   .Select(g => new {g.Key.A, g.Key.B, MyCount = g.Count()});

or you can have this query

var result = from MyObjs in MyList
group MyObjs by new { MyObjs.A, MyObjs.B } into g
select new { g.Key.A, g.Key.B, MyCount = g.Count() }

now you can loop through each of the list data using foreach and use the above query to Count & group by each list item.

For three columns your query must be like below

 var result = context.ColumnChecks.GroupBy(x => new { x.ColumnA, x.ColumnB,x.ColumnC })
.Select(g => new { g.Key.ColumnA, g.Key.ColumnB,g.Key.ColumnC, MyCount = g.Count() });

Considering your table is

here is the results while executing the above query on the given sql table

 A first try for the foreach statement was

foreach (var line in list.GroupBy(c => new  { c.Dep1, c.Dep2 })
                   .Select(group => new {
                        A3_1 = group.Key.Dep1,
                        A3_2 = group.Key.Dep2
                         Count = group.Count()
                    })

 How can I have a count for each of the fields  add the result to my list? thank you

Hello thank you for your  reply.  I am going to try your code

Sorry if my code is poor

I have try this in my sql server which returns the result I need

SELECT Dep1, Dep2,

count(Dep1) as Dep1count,
   
count(Dep2) as Dep2count 

from Surveys GROUP BY Dep1, Dep2

The problem is that I have a lot of departments thats why I need the forech loop. I need to count all the values in the deptartments columns (The dep1 dep2 is just for example.) Thank you again

Thank you for your reply.... Well look what happened in my case. MyCount is always returns 1

this is what I try

var result = list.GroupBy(x => new { x.A3_1, x.A3_2, x.A3_3, x.A3_4 })
                   .Select(g => new {g.Key.A3_1, g.Key.A3_2, g.Key.A3_3, g.Key.A3_4, MyCount = g.Count() });

I also try to run the query on the table but I had same result

var result = db.MyTable(x => new { x.A3_1, x.A3_2, x.A3_3, x.A3_4 })
                   .Select(g => new {g.Key.A3_1, g.Key.A3_2, g.Key.A3_3, g.Key.A3_4, MyCount = g.Count() });

Any idea? thank you