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


Hello,

I need to write a foreach statement (I don't have it yet) in which to group by and count the departments.

This is what I have done so far. How can I take the count of each column?

thank you

var s = list.GroupBy(c => new { c.Dep1, c.Dep2})

                .Select(group => new
                {
                    A3_1 = group.Key.Dep1,
                    A3_2 = group.Key.Dep2,
                    Count = group.Count()


                });

Asked by:- mspace
0
: 12536 At:- 2/2/2018 8:24:48 AM
linq mvc C#

what are you trying to do here? can you elaborate more, doesn't look like a proper question with details 0
By : vikas_jk - at :- 2/2/2018 11:47:14 AM






5 Answers
profileImage Answered by:- Sam

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.

3
At:- 2/2/2018 11:59:57 AM
thank you very much your code is working perfect.... 0
By : mspace - at :- 2/2/2018 12:40:29 PM
Well.. When I add a third column the count doesn't work thank you 0
By : mspace - at :- 2/2/2018 1:18:07 PM
for 3 columns your query must be as 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() }); 0
By : Sam - at :- 2/3/2018 7:21:10 AM


profileImage Answered by:- Sam

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

sql-table.png

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

result-linq-select-multiple-group-by-and-count-min.png

3
At:- 2/3/2018 7:34:46 AM
Its ok now...I empty my table and it worked with some new records...thank you very much 0
By : mspace - at :- 2/5/2018 6:57:06 AM


profileImage Answered by:- mspace

 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

0
At:- 2/2/2018 8:30:41 AM Updated at:- 2/2/2018 12:26:29 PM


profileImage Answered by:- mspace

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

0
At:- 2/2/2018 12:36:03 PM Updated at:- 2/2/2018 12:36:48 PM


profileImage Answered by:- mspace

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

 

 

 

0
At:- 2/5/2018 7:58:28 AM
Check your database columns properly, or attach an image , looks everything ok to me in C# code. Group By will return count=2 when there are two rows which have exact same value in all 4 columns, otherwise count =1 0
By : Sam - at :- 2/5/2018 10:32:48 AM
I suppose the problem is that I have diffrent values. Is there any change to have the count for diffrent values? thank you 0
By : mspace - at :- 2/5/2018 10:56:37 AM
Different values means it will be counted as 1 & I don't think we can change C# compiler settings to calculate it differently :) 0
By : Sam - at :- 2/5/2018 6:28:16 PM
:) ...problema!!! 0
By : mspace - at :- 2/6/2018 6:32:49 AM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use