title: Linq: Getting subtotals for the inner objects with multiple nested groups
body: <p>I need to create some reports from a dataset retrieved from a SQL stored procedure. Here is a representation of the data:</p>
  <pre><code>+--------------+-------------------+-----------------+--------------------+
  | CustomerName | CustInventoryAcct | ProductCategory | ProductSubCategory |
  +--------------+-------------------+-----------------+--------------------+
  | ABC          |             12345 | Books           | Fiction            |
  | ABC          |             12345 | Books           | Fiction            |
  | ABC          |             12345 | Books           | Non-Fiction        |
  | ABC          |             67890 | Magazines       | Sports             |
  +--------------+-------------------+-----------------+--------------------+
  </code></pre>
  <p>The dataset also includes many data fields related to the actual product that I'm omitting for brevity - the above are the four criteria I need to group on.</p>
  <p>The report needs to group on each of those four fields, so using the above table the output would look like this:</p>
  <pre><code>Customer Header: ABC
      Inventory Header: 12345
          Category Header: Books
              Sub Category Header: Fiction
                  Detail rows for each of the items in this group (2 using the sample data above)
              Sub Category Footer: Fiction (Item Count : 2)
              Sub Category Header: Non-Fiction
                  Detail row
              Sub Category Footer: Non Fiction (Item Count: 1)
          Category Footer: Books (Item Count: 3)
      Inventory Footer: 12345 (Item Count: 3)
      Inventory Header: 67890
          Category Header: Magazines
              Sub Category Header: Sports
                  Detail row
              Sub Category Footer: Sports (Item Count: 1)
          Category Footer: Magazines (Item Count: 1)
      Inventory Footer: 67890 (Item Count: 1)
  Customer Footer: ABC (Item Count: 4)
  </code></pre>
  <p>I have a Linq query that is working for the grouping (not sure if this is the best way to do this):</p>
  <pre><code>var groupedData = rawData
      .GroupBy(x =&gt; new { x.ProductSubCategory, x.ProductCategory, x.CustInventoryAcct, x.Customer })
      .GroupBy(x =&gt; new { x.Key.ProductCategory, x.Key.CustInventoryAcct, x.Key.Customer })
      .GroupBy(x =&gt; new { x.Key.CustInventoryAcct, x.Key.Customer })
      .GroupBy(x =&gt; new { x.Key.Customer })
  </code></pre>
  <p>I use nested <code>foreach</code> loops to build the report structure:</p>
  <pre><code>foreach (var customer in groupedData)
  {
      GenerateHeader(customer.Key);
      foreach (var custInventory in customer)
      {
          GenerateHeader(custInventory.Key.CustInventoryAcct);
          foreach(var category in custInventory)
          {
              GenerateHeader(category.Key.ProductCategory);
              foreach(var subCategory in category)
              {
                  GenerateHeader(subCategory.Key.ProductSubCategory);
                  foreach (var item in subCategory)
                      // Populate detail rows
                  // Generate footer
               }
               // Generate footer
          }
          // Generate footer
      }
      // Generate footer    
  }
  // Generate footer
  </code></pre>
  <p>This part is working great - the problem is when I am trying to get the item counts for each footer. I can get the item count at the last nested level (SubCategory) by using <code>Count()</code>, but if I try that on the higher levels it predictably gives me the count of groups in that level, not the underlying items at the bottom of the hierarchy. I can't figure out how to drill down so I can create subtotals for each level of nesting.</p>
  <p>I can get around this by using some <code>int</code> counters, but I was wondering if there was a way to do this in Linq. The difficulty I'm having drilling down makes me wonder if my grouping query is not the right way to handle nested groups in Linq.</p>
  <p>I've read a bunch of SO answers and articles on using <code>GroupBy</code> but still can't figure it out - can someone help me understand the best way to accomplish what I am trying to do?</p>