CODEWITHSUNDEEP
linq-to-xml
Graeme
Graeme

Reputation: 2667

Grouping and summing in LINQ to XML

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 I had 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));

How can I fix it?

Upvotes: 1

Views: 1261

Answers (2)

Graeme
Graeme

Reputation: 2667

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)
  };

Upvotes: 1

Eamon Nerbonne
Eamon Nerbonne

Reputation: 48146

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.

Upvotes: 2

Related Questions