none
Grouping By one column, and Count in the next RRS feed

  • Question

  • I have a table called Learner, and it contains two columns. LearnRefNumber and LearnAimRef. 

    What I want to to, and I think using LINQ will be best here, is to take from this table, the distinct LearnRefNumber as one column, and another column to be a Count of the LearnAimRef associated to that LearnRefNumber. This will then be used to add to a separate data table.

    I am rather stuck and getting myself into knots with this. 

    Tuesday, June 25, 2019 11:02 AM

Answers

  • Hello,

    It would be prudent to show what you have tried so far. Until then check out Group Elements in a Sequence which has a good start. All examples uses classes but the same can be done with DataTable if that is what you mean by table, it's unclear.

    Here is an example using a DataTable, I didn't bother doing an order by but you can if so desire.

    public class Item
    {
        public int NameCount { get; set; }
        public int Serial { get; set; }
        public IEnumerable<int> Data { get; internal set; }
    }

    Mocked up data

    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var dt = new DataTable { TableName = "MyTable" };
    
                dt.Columns.Add(new DataColumn { ColumnName = "Serial", DataType = typeof(int) });
                dt.Columns.Add(new DataColumn { ColumnName = "Name", DataType = typeof(string) });
                dt.Columns.Add(new DataColumn { ColumnName = "Date", DataType = typeof(DateTime) });
    
    
    
                dt.Rows.Add(222, "IBM", new DateTime(2016, 1, 13));
                dt.Rows.Add(111, "Microsoft", new DateTime(2017, 1, 12));
                dt.Rows.Add(333, "Apple", new DateTime(2010, 5, 15));
                dt.Rows.Add(111, "Microsoft", new DateTime(2017, 1, 1));
                dt.Rows.Add(222, "IBM", new DateTime(1980, 12, 12));
    
                List<Item> studentData = dt.AsEnumerable()
                    .GroupBy((student) => student.Field<int>("Serial"))
                    .Select((group) => new Item
                    {
                        Serial = group.Key,
                        NameCount = group.Select(x => x.Field<string>("Name")).Count(),
                        Data = group.Select(x => x.Field<int>("Name"))
                    }).ToList();
    
                foreach (var item in studentData)
                {
                    Console.WriteLine($"Item {item.Serial} Name Count {item.NameCount} ");
                }
    
            }
        }
    }
    

    Results

    Item 222 Name Count 2 
    Item 111 Name Count 2 
    Item 333 Name Count 1 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Tuesday, June 25, 2019 11:37 AM
    Moderator