CODEWITHSUNDEEP
linq-to-sql
Jes Gudiksen
Jes Gudiksen

Reputation: 452

Linq To SQL Group By and Sum

Her is an image showing a the table i have, b the grid i need to display. opps cant post image. ill try to explain. My table have four colums.

  1. Project Number(String)
  2. ItemNumber(String)
  3. Location(String)
  4. Qty.(Real).

My grid need to look like this.

  1. ProjectNumber
  2. ItemNumber
  3. QtyMain.
  4. QtyOther.

I Need to write a linq query Grouping evry line so i will se 1 Line pr Project/ItemNumber combination summing qty into 2 differetn colums 1 showing to qty where location is main and 1 showing the qty where location is not (!=) main. Can linq do this for me, or how can thsi be done?

Upvotes: 8

Views: 11611

Answers (1)

Brad Christie
Brad Christie

Reputation: 101604

public class Foo
{
    public Int32 ProjectNumber;
    public String ItemNumber;
    public String InventLocation;
    public Int32 Qty;
}

void Main()
{
    List<Foo> foos = new List<Foo>(new[]{
        new Foo { ProjectNumber = 1, ItemNumber = "a", InventLocation = "Main", Qty = 3 },
        new Foo { ProjectNumber = 1, ItemNumber = "a", InventLocation = "Main", Qty = 3 },
        new Foo { ProjectNumber = 1, ItemNumber = "a", InventLocation = "Sub", Qty = 2 },
        new Foo { ProjectNumber = 1, ItemNumber = "a", InventLocation = "Sub", Qty = 1 },
        new Foo { ProjectNumber = 1, ItemNumber = "a", InventLocation = "Sub2", Qty = 5 }
    });

    var foo = from f in foos
              group f by new { f.ProjectNumber, f.ItemNumber } into fGroup
              select new {
                ProjectNumber = fGroup.Key.ProjectNumber,
                ItemNumber = fGroup.Key.ItemNumber,
                QtyMain = fGroup.Where (g => g.InventLocation == "Main").Sum (g => g.Qty),
                Rest = fGroup.Where (g => g.InventLocation != "Main").Sum (g => g.Qty)
              };
    foo.Dump();
}

Resulting in:

IEnumerable<> (1 item)  
ProjectNumber ItemNumber QtyMain Rest 
1             a          6       8

Upvotes: 10

Related Questions