Group on Two Columns and Create Two Separate Counts

Question

Question

We have a table of StudentId and LectureId, and we want to know two things.

1. CountStudentId How many times each StudentId occurs.
2. CountStudentIdLectureId How many times each StudentId + LectureId pair occurs.

(2) is done in below. (1) is not.

In other words, how can we count two different groups in a single query?

Another way of thinking about this, would be to count the StudentId + LectureId group and also sum that count for each StudentId.

What we've tried

The following query groups on StudentId + LectureId. It does count how many times the StudentId + LectureId group occurs. It doesn't count how many times the StudentId group occurs. That's what we also want.

var query = joinTable
    .GroupBy(jt => new { jt.StudentId, jt.LectureId } )
    .Select(g => new {
        StudentId = g.Key.StudentId,
        LectureId = g.Key.LectureId, 
        CountStudentId =  -1, // Count all StudentId (i.e. 10)?
        CountStudentIdLectureId = g.Count()
    });

This is the result we're currently receiving. In each row, the -1 value should be 10 (because we seeded the JoinTable with ten of each StudentId) and we haven't achieved that.

Results we want to achieve

...but with 10 instead of -1 in each case.**

StudentId      LectureId      CountStudentId   CountStudentLectureId
0              0              -1               3              
0              1              -1               3              
0              2              -1               3              
0              3              -1               1              
1              0              -1               2              
1              1              -1               3              
1              2              -1               3              
1              3              -1               2

In those results, we need CountStudentId to be 10 not -1 (the latter is just a placeholder for now.)

That's the expected result, because each StudentId occurs 10 times and because the sum of CountStudentLectureId for each StudentId is 10, which is just two ways of saying the same thing.

Full demo code

This is the full Fiddle code for reference.

using System;
using System.Linq;
using System.Collections.Generic;

public static class Program
{
    public static void Main()
    {
        var joinTable = SeedJoinTable();

        var query = joinTable
            .GroupBy(jt => new { jt.StudentId, jt.LectureId } )
            .Select(g => new {
                StudentId = g.Key.StudentId,
                LectureId = g.Key.LectureId, 
                CountStudentId =  -1, // Count all StudentId (i.e. 10)?
                CountStudentIdLectureId = g.Count()
            });

        // this is just the printing of the results
        Console.WriteLine(
            "StudentId".PadRight(15) +
            "LectureId".PadRight(15) +
            "CountStudentId".PadRight(17) +
            "CountStudentLectureId".PadRight(15));

        foreach(var x in query)
        {
            Console.WriteLine(string.Format("{0}{1}{2}{3}", 
                x.StudentId.ToString().PadRight(15), 
                x.LectureId.ToString().PadRight(15), 
                x.CountStudentId.ToString().PadRight(17), 
                x.CountStudentIdLectureId.ToString().PadRight(15)));
        }
    }

    public static List<JoinTable> SeedJoinTable()
    {
        var list = new List<JoinTable>();
        var studentId = 0;
        var lectureId = 0;

        // insert 20 records
        for(int i = 0; i < 20; ++i)
        {
            if(i != 0)
            {
                if(i % 10 == 0) 
                {   
                    // 10 of each studentId
                    ++studentId;
                    lectureId = 0;
                }
                if(i % 3 == 0)
                {
                    // 3 of each lectureId per student
                    ++lectureId;
                }
            }

            list.Add(new JoinTable() { 
                StudentId = studentId, 
                LectureId = lectureId 
            });
        }
        return list;
    }

    public class JoinTable
    {
        public int StudentId { get; set; }
        public int LectureId { get; set; }
    }
}

Answer 1

Here is a working DotNotFiddle that produces the results that you want to achieve.

You will want to group by StudentId and set the value to LectureId. This allows you to get the count of both studentId and studentIdLectureId pairs.

        var query = joinTable
        .GroupBy(jt => jt.StudentId, jt => jt.LectureId)
        .Select(x => 
             new {
            StudentId = x.Key,
            CountStudentId = x.Count(),
            LectureIds = x.GroupBy(y => y),
        });

This does alter how you will loop through the final list, but will provide you the same data with the same amount of loops:

    foreach(var x in query)
    {
        foreach(var lectureId in x.LectureIds)
        {
            Console.WriteLine(string.Format("{0}{1}{2}{3}", 
                x.StudentId.ToString().PadRight(15), 
                lectureId.Key.ToString().PadRight(15), 
                x.CountStudentId.ToString().PadRight(17), 
                lectureId.Count().ToString().PadRight(15)));
        }
    }

If you want to include anything with the lectureId (lecture name, professor, etc.) you can do so like this:

        var query = joinTable
        .GroupBy(jt => jt.StudentId, jt => new {LectureId = jt.LectureId, ProfessorId = jt.ProfessorId})
        .Select(x => 
             new {
            StudentId = x.Key,
            CountStudentId = x.Count(),
            LectureIds = x.GroupBy(y => y),
        });