Counting records in C# using LINQ

Question

I have a simple SQL query:

Select ID, COUNT(ID) as Selections, OptionName, SUM(Units) as Units
FROM tbl_Results
GROUP BY ID, OptionName

The results I got were:

'1' '4' 'Approved'    '40' 
'2' '1' 'Rejected'    '19'
'3' '2' 'Not Decided' '12'

I have to encrypt the data in the database, and as such am unable to sum the data in relational form. To get around this I decrypt the data in the application layer, and want to use LINQ to query it there. I need the following results:

'1' 'Approved'     '10'
'3' 'Not Deceided' '6'
'2' 'Rejected'     '19'
'1' 'Approved'     '15'
'1' 'Approved'     '5'
'3' 'Not Deceided' '6'
'1' 'Approved'     '10'

I put these results into class and create a strongly typed list:

public class results
{
 public int ID {get;set;}
 public string OptionName {get;set;}
 public int Unit {get;set;}
}

I almost have the LINQ query to bring back the results like the SQL query about:

var q = from r in Results
        group p.Unit by p.ID
        int g
        select new {ID = g.Key,
                    Selections = g.Count(),
                    Units = g.Sum()};

How do I ensure my LINQ query also give me the Option Name?

If I created a class called Statistics to hold my results how would I modify the LINQ query to give me List<Statistics> result set?

public class results
    {
     public int ID {get;set;}
     public int NumberOfSelections { get; set; }
     public string OptionName {get;set;}
     public int UnitTotal {get;set;}
    }

Answer 1

This query groups by id and option name thus making it available:

var q = from r in Results
        group p.Unit by new { ID = p.ID, OptionName = p.OptionName } into g
        select new {
            ID = g.Key.ID,
            OptionName = g.Key.OptionName,
            Selections = g.Count(),
            Units = g.Sum()
        };

To return your custom result class use:

var q = from r in Results
        group p.Unit by new { ID = p.ID, OptionName = p.OptionName } into g
        select new results() {
            ID = g.Key.ID,
            OptionName = g.Key.OptionName,
            NumberOfSelections = g.Count(),
            UnitTotal = g.Sum()
        };

Answer 2

You're currently just grouping by the ID. Instead, as per the original query, you need to group by the option name too. That way each group's key will contain both the ID and the option name. Here's just that change:

var q = from r in Results
        group r.Unit by new { p.ID, p.OptionName } into g
        select new { ID = g.Key.ID,
                     OptionName = g.Key.OptionName
                     Selections = g.Count(),
                     UnitTotal = g.Sum() };

Now, it wasn't immediately obvious to me that you were selecting just the "unit" part for the group item. I missed "group p.Unit by" in the query expression. My bad... but others may do likewise. Here's an alternative, which makes the group contain the items, and then sums the units in the projection:

var q = from r in Results
        group r by new { p.ID, p.OptionName } into g
        select new { ID = g.Key.ID,
                     OptionName = g.Key.OptionName
                     Selections = g.Count(),
                     UnitTotal = g.Sum(x => x.Unit) };