locked
Please help with Linq + Group & Sum... RRS feed

  • Question

  • Hey

    I have a table called TagAb

    In this table, I have two fields called Name & Price
    Now, I want to be able , that I have total Price for each name. - So I have to group the name and get the sum out of the grouped names...

    But I don't get it with LinQ

    Please give me an example.

    THX a lot !

    Sunday, June 6, 2010 3:46 PM

Answers

  • Hi Zero-G,

    If you are using Linq to Sql, you can do something like this:

    NorthwindDataContext db = new NorthwindDataContext();
    var results = from p in db.Order_Details  //Change Order_Details to TagAb
           group p by p.OrderID into g //Change OrderID to your filed "Name"
           select new
           {
             OrderID = g.Key,
             TotalPrice = g.Sum(u => u.UnitPrice) //Change UnitPrice to your filed "Price"
           };
    
    this.dataGridView1.DataSource = results.ToList();
    

    If you are using Linq to DataSet, then you can do something like this:

    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    string strSQL = "Select * from [Order Details]"; //Change [Order Details] to TagAb
    
    SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);
    DataTable dt = new DataTable();
    
    da.Fill(dt);
    
    var results1 = from p in dt.AsEnumerable()
            group p by p["OrderID"] into g  //Change OrderID to your filed "Name"
           select new
           {
             OrderID = g.Key,
             TotalPrice = g.Sum(u => (decimal)u["UnitPrice"]) //Change UnitPrice to your filed "Price"
           };
    
    this.dataGridView2.DataSource = results1.ToList();
    

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Liang Sunday, June 13, 2010 9:29 AM
    Tuesday, June 8, 2010 7:44 AM
  • You can use below example to get above functionality.  Please note I have just created class to create example. You can continue with your Table for same.

      public class TagAb
        {
          public string Name;
          public Int32 Price;
        }
    
        public MainWindow()
        {
          InitializeComponent();
    
          List<TagAb> items = new List<TagAb>();
          items.Add(new TagAb() { Name = "A", Price = 20 });
          items.Add(new TagAb() { Name = "A", Price = 10 });
          items.Add(new TagAb() { Name = "B", Price = 20 });
          items.Add(new TagAb() { Name = "B", Price = 30 });
          var results = items.GroupBy(row => row.Name).Select(row => new { Name = row.Key, Count = row.Sum(rowInner => rowInner.Price) });
          foreach (var item in results)
          {
            MessageBox.Show(item.Name);
            MessageBox.Show(item.Count.ToString());
          }                       
    
        }


    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Alex Liang Sunday, June 13, 2010 9:29 AM
    Sunday, June 6, 2010 4:08 PM

All replies

  • You can use below example to get above functionality.  Please note I have just created class to create example. You can continue with your Table for same.

      public class TagAb
        {
          public string Name;
          public Int32 Price;
        }
    
        public MainWindow()
        {
          InitializeComponent();
    
          List<TagAb> items = new List<TagAb>();
          items.Add(new TagAb() { Name = "A", Price = 20 });
          items.Add(new TagAb() { Name = "A", Price = 10 });
          items.Add(new TagAb() { Name = "B", Price = 20 });
          items.Add(new TagAb() { Name = "B", Price = 30 });
          var results = items.GroupBy(row => row.Name).Select(row => new { Name = row.Key, Count = row.Sum(rowInner => rowInner.Price) });
          foreach (var item in results)
          {
            MessageBox.Show(item.Name);
            MessageBox.Show(item.Count.ToString());
          }                       
    
        }


    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Alex Liang Sunday, June 13, 2010 9:29 AM
    Sunday, June 6, 2010 4:08 PM
  • Hi Zero-G,

    If you are using Linq to Sql, you can do something like this:

    NorthwindDataContext db = new NorthwindDataContext();
    var results = from p in db.Order_Details  //Change Order_Details to TagAb
           group p by p.OrderID into g //Change OrderID to your filed "Name"
           select new
           {
             OrderID = g.Key,
             TotalPrice = g.Sum(u => u.UnitPrice) //Change UnitPrice to your filed "Price"
           };
    
    this.dataGridView1.DataSource = results.ToList();
    

    If you are using Linq to DataSet, then you can do something like this:

    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    string strSQL = "Select * from [Order Details]"; //Change [Order Details] to TagAb
    
    SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);
    DataTable dt = new DataTable();
    
    da.Fill(dt);
    
    var results1 = from p in dt.AsEnumerable()
            group p by p["OrderID"] into g  //Change OrderID to your filed "Name"
           select new
           {
             OrderID = g.Key,
             TotalPrice = g.Sum(u => (decimal)u["UnitPrice"]) //Change UnitPrice to your filed "Price"
           };
    
    this.dataGridView2.DataSource = results1.ToList();
    

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Liang Sunday, June 13, 2010 9:29 AM
    Tuesday, June 8, 2010 7:44 AM