How can I add this stuff up that is coming from a database?

Question

Hi

Say I have this in my database

courseName : English101
Mark : 80
OutOff : 100
Weight : 50

CourseName : English101
Mark : 30
OutOff : 50
Weight : 10

CourseName: Chem101
Mark  : 100
OutOff: 100
Weight : 100

Now I want to find the over all grade for both of these courses

First english101.

(80 /100) * 50 = 40(achieved weight) (30 / 50) * 10 = 6

40 + 6 = 46 / 100(course weight always will total up to 100% - this means that 40% of the weight has not been distributed yet but still counts again the overallMark.)

so if this student would drop out of english101 their final mark would be 46%.

Now I don't know how to do this at all. At first I tried to add up all the marks, outOff and weights by using Group by and Sum functions when retrieving from the database.

However this gives different percents than what I am expecting. So I need to do it row by row.

So I have to add each row up then take all the rows say from English101 and add up their achieved weight.

But I don't how to do this. I tried to use a dictionary and use the courseName as the key and then check if the next row coming in had the same courseName. Then I would pull up the current value from that dictionary and add it to it.

But I only made it half way through of making it and I had 1 foreach loop and 3 nested if statements.

So there should be a better way.

I am using linq to sql. I am not sure I can use that to do all this while it is coming from the database.

Answer 1

How about:

var results = from course in courses
              where course.OutOf != 0
              group course by course.CourseName into grouped
              select new { CourseName = grouped.Key,
                           Mark = grouped.Sum(item => 
                                    (item.Mark * item.Weight) / item.OutOf) };

Test in LINQ to Objects:

using System;
using System.Linq;

class Test
{
    static void Main(string[] args)
    {
        var courses = new[]
        {
            new { CourseName = "English101", Mark = 80, OutOf = 100, Weight =50},
            new { CourseName = "English101", Mark = 30, OutOf = 50, Weight = 10},
            new { CourseName = "Chem101", Mark = 100, OutOf = 100, Weight = 100 }
        };

        var results = from course in courses
              where course.OutOf != 0
              group course by course.CourseName into grouped
              select new { CourseName = grouped.Key,
                           Mark = grouped.Sum(item => 
                                    (item.Mark * item.Weight) / item.OutOf) };

        foreach (var result in results)
        {
            Console.WriteLine(result);
        }
    }
}

I don't know whether you'll need to worry about integer arithmetic and division when it's done in the database... you may not want the result of each bit of the sum to be truncated.

The fact that some rows will be missing is irrelevant, because the weight of the valid rows will still add up to the appropriate amount.