# Group by a group

• ### Question

• I have two tables. One contains one or more part numbers associated with a vendor. So you might see something like:

PartNumber Vendor
1                A
2                B
3                A
4                B

The other table gives be orders. So this table looks like:

PartNumber Date       Quantity
1                1/1/2009 10
2                1/2/2009 2
3                1/1/2009 5
4                1/3/2009 6

Because of other queries I form a List<> of orders that fall with in a certain date range. The partial query that I have come up with so far is:

```                        var orders = from o in orderHistory
group o by ??? into vendorList
select new
{
Vendor = vendorList.Key,
Summary = from h in vendorList
group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
orderby skuHistory.Key
select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
};
```

The question is what to put in the ??? spot. The orderHistory is the List<> of orders that I described above and the selection groups the orders into a period. Basically I want this group to group all of the part numbers (skus) that are tied to a particular vendor (as specified by the table described above) and the associated vendor name to be in the vendorList.Key. I am not sure how to frame this query. Any help?

Thank you.
Wednesday, June 10, 2009 5:25 PM

• Try the folloving decision. I suppose that both tables are in 1to1 relation

```var orders = from o in orderHistory
join v in Vendors on o.PartNumber equals v.PartNumber                                      group new { o, v } by o.Vendor into vendorList                                     select new
{
Vendor = vendorList.Key,
Summary = from h in vendorList.o
group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
orderby skuHistory.Key
select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
};

```
• Marked as answer by Thursday, June 11, 2009 7:08 PM
• Edited by Friday, June 12, 2009 8:00 AM
Thursday, June 11, 2009 5:39 PM

### All replies

• Have you tried "o.Vendor"?

Also, is this a LINQ to SQL query?  And if so, do you have association properties set up?

Can you tell us the final result that you're after?

Joe

Write LINQ queries interactively - www.linqpad.net
Thursday, June 11, 2009 12:50 AM
• The orderHistory is generated with a LINQ to SQL query but as above it is just a List<T>.

In the end I want a sequence that is grouped by Vendor.  So still using the above sample data I would end up with a swquence like:

Vendor Date       Quantity
A         1/1/2009 15
B         1/2/2009   2
B         1/3/2009   6

I haven't further grouped the sequence into a period (which is what the sub-query does) as that may complicate matters.

Kevin

Thursday, June 11, 2009 3:27 PM
• Try the folloving decision. I suppose that both tables are in 1to1 relation

```var orders = from o in orderHistory
join v in Vendors on o.PartNumber equals v.PartNumber                                      group new { o, v } by o.Vendor into vendorList                                     select new
{
Vendor = vendorList.Key,
Summary = from h in vendorList.o
group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
orderby skuHistory.Key
select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
};

```
• Marked as answer by Thursday, June 11, 2009 7:08 PM
• Edited by Friday, June 12, 2009 8:00 AM
Thursday, June 11, 2009 5:39 PM
• Thank you. I am unfamiliar with the new {o,v} syntax. This was the magic I guess I was missing. Can you elighten me as to what this statement along with the group does? Thanks.

Kevin

Thursday, June 11, 2009 7:08 PM
• To undestand this see Expression's which are generated for this query. "new {o,v}" becomes a source for Queryable.GroupBy. And "group" is translated to the next

```.GroupBy(
source => source.O.Vendor, // key selector
source => new vendorListAnonymousType0`2( //result selector
c = source.c,
o = source.o
)
)
```
Friday, June 12, 2009 8:09 AM
• I am sorry but Vendor is not a member of o (orderHistory). The orderHistory only has part numbers. The Vendors table would have the mapping from part number to vendor.

So the Vendor table basically looks like:

Vendor PartNumber

And the order table (order history) has

PartNumber Date Quantity

Also there can be a (and often is) more than one vendor for a given part number.

Kevin
Friday, June 12, 2009 8:32 PM
• Excuse me, of course the correct method call is the following

```.GroupBy(
source => source.v.Vendor, // key selector
source => new { //result selector
o = source.o,
v = source.v
}
)```

where "source" and result selector are the complex object "new {o,v}".

Friday, June 12, 2009 10:14 PM
• The problem that I don't understand is the 'group new {o,v} by o.Vendor' when o.Vendor doesn't exist. The Vendors table (v) is the only table that has the name/identifier of the vendor in it.

Thanks again.

Kevin
Friday, June 12, 2009 10:19 PM
• It is mistake.
```var orders = from o in orderHistory
join v in Vendors on o.PartNumber equals v.PartNumber
group new { o, v } by v.Vendor into vendorList
select new
{
Vendor = vendorList.Key,
Summary = from h in vendorList.o
group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
orderby skuHistory.Key
select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
};
```
Saturday, June 13, 2009 5:04 AM