Grouping and summing in Linq to XML

Question

Be most grateful if someone could help with this. Given the XML below, I need to group by InputItem and sum the costs for all years. So the results should be

1. Research Contracts, 7000
2. Technical Contracts, 5000
3. Hospitality, 2000

Here's the XML:

<Inputs>
<Inputs_Table_Row>
  <InputItem>Research Contracts</InputItem>
  <TotalYear1>1000</TotalYear1>
  <TotalYear2>2000</TotalYear2>
</Inputs_Table_Row>
<Inputs_Table_Row>
  <InputItem>Research Contracts</InputItem>
  <TotalYear1>2000</TotalYear1>
  <TotalYear2>2000</TotalYear2>
</Inputs_Table_Row>
<Inputs_Table_Row>
  <InputItem>Technical Contracts</InputItem>
  <TotalYear1>1000</TotalYear1>
  <TotalYear2>2000</TotalYear2>
 </Inputs_Table_Row>
<Inputs_Table_Row>
  <InputItem>Technical Contracts</InputItem>
  <TotalYear1>1000</TotalYear1>
  <TotalYear2>1000</TotalYear2>
</Inputs_Table_Row>
<Inputs_Table_Row>
  <InputItem>Hospitality</InputItem>
  <TotalYear1>1000</TotalYear1>
  <TotalYear2>1000</TotalYear2>
</Inputs_Table_Row>
</Inputs>

Here's my attempt simply to group so far, but no success:

XDocument doc = XDocument.Load(@"c:\temp\CrpTotalsSimple.xml");

var query = from c in doc.Descendants("Inputs_Table_Row")
            group new
            {
                Item = (string)c.Element("InputItem")
            }
            by c.Element("InputItem") 
            into groupedData

            select new
            {
                ItemName = groupedData.Key.Value
             };

foreach (var item in query)
    Console.WriteLine(String.Format("Item: {0}", item.ItemName));

Answer 1

You need something like...

var query = from yearTotalEl in doc.Descendants()
            where yearTotalEl.Name.LocalName.StartsWith("TotalYear")
            group (decimal)yearTotalEl 
            by (string)yearTotalEl.Parent.Element("InputItem") into g
            select new {ItemName = g.Key, Sum = g.Sum()};

I'm using decimal because I suspect you're dealing with a currency that may have a fractional component - int would otherwise be fine.

Answer 2

Looks like I was just missing using Value on the group by clause. Here's my working solution:

var query = from c in doc.Descendants("Inputs_Table_Row")
group new
{
    Item = c.Element("InputItem").Value,
    ItemValue = (int)c.Element("TotalYear1") + (int)c.Element("TotalYear2")
 }
 by c.Element("InputItem").Value 
 into groupedData

 select new
 {
      ItemName = groupedData.Key,
      ItemTotal = groupedData.Sum(rec => rec.ItemValue)
  };