locked
SUM a LINQ Sub-query RRS feed

  • Question

  • Hi,

    I have a large datatable consisting of four columns: Date, Site, Sales and SOH

    I am trying to calculate the current SOH (Stock on Hand) by Site, this requires that I group the sites, and SUM all the SOH in the last date through a LINQ query.

    The following query and sub-query works but supplies all the records that match the above criteria,

    what I cannot figure how to SUM the SOH to GROUP by store. I have searched and tried so may ways to achieve this without any luck.

    Thanks for any help you can afford me?

    var StoreSOH =
                                    from row in SalesDT.AsEnumerable()
                                    group row by row.Field<int>("Site") into grp
                                    select new
                                    {
                                        Site = grp.Key,
                                        Date = grp.Max(t => t.Field<DateTime>("Date")),
                                        //SOH = grp.Sum(r => r.Field<int>("SOH"))
                                    };
                                var StoreSOH2 = from row in StoreSOH
                                                join line in SalesDT.AsEnumerable() on row.Site equals line.Field<int>("Site")
                                                where line.Field<DateTime>("Date") == row.Date
                                                select new
                                                {
                                                    Site = row.Site,
                                                    Date = row.Date,
                                                    SOH = line.Field<int>("SOH")
                                                };

    • Moved by Kristin Xie Wednesday, January 13, 2016 8:56 AM Linq related
    Thursday, January 7, 2016 2:57 PM

Answers

  • HI BobbyZA,

    Please try the following query.

    var StoreSOH = from row in dt.AsEnumerable()
    
                            let id = row.Field<int>("Site")
    
                            group row by id into grp
    
                            orderby grp.Key
    
                            select new
    
                            {
    
                                id = grp.Key,
    
                                Date = grp.Max(t => t.Field<DateTime>("Date")),
    
                                Sum = grp.Sum(r => r.Field<int>("SOH"))
    
                            };
    
     
    
               
    
     
    
                var StoreSOH2 = from d in StoreSOH
    
                                join line in dt.AsEnumerable() on d.id equals line.Field<int>("Site")
    
                                where line.Field<DateTime>("Date") == d.Date
    
                                select new
    
                                {
    
                                    Site = d.id,
    
                                    Date = d.Date,
    
                                    SOH = line.Field<int>("SOH")
    
                                };
    

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by BobbyZA Friday, January 15, 2016 11:52 AM
    Friday, January 15, 2016 5:53 AM

All replies

  • http://www.codethinked.com/grouping-linq-aggregates-in-c
    Thursday, January 7, 2016 3:23 PM
  • have a look at a linq query for group by.

    var totalProducts = ctx.Orders
                           .Join(ctx.Products, o => o.ProductId, p => p.Id,
                                 (o, p) => new { p.Name, o.NumberOf })
                           .GroupBy(t => t.Name,
                                    pair => pair.Name, // Key selector
                                    pair => pair.NumberOf, // Element selector
                                    (key, numbers) => new { 
                                        ProductName = key,
                                        TotalOrdered = numbers.Sum()) 
                                    });

    Or:

    var totalProdcuts = from o in ctx.Orders
                        join p in ctx.Products on o.ProductId equals p.Id
                        group o.NumberOf by p.Name into g
                        select new { ProductName = g.Key, TotalOrdered = g.Sum() };


    Ashish Pandey

    Thursday, January 7, 2016 5:50 PM
  • Try this query:

    var results =
            from row in SalesDT.AsEnumerable()
            group row by row.Field<int>( "Site" ) into grp
            select new
               {
                   Site = grp.Key,
                   Sum = ( from r in grp where r.Field<DateTime>( "Date" ).Date == grp.Max( z => z.Field<DateTime>( "Date" ).Date ) select r.Field<int>( "SOH" ) ).Sum()
               };



    • Edited by Viorel_MVP Thursday, January 7, 2016 6:24 PM
    • Proposed as answer by Venkat786 Thursday, January 7, 2016 7:51 PM
    Thursday, January 7, 2016 6:15 PM
  • Hi Venkat786,

    Thanks, that works for the smaller test data but for some reason not for the main data. I am reasonably sure that the data is not corrupt as my previous code works. Any pointers?

    Friday, January 8, 2016 12:10 PM
  • Hi BobbyZA,

    Based on your description, your case more related to Linq query, I will move your case to that forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Kristin Xie Wednesday, January 13, 2016 8:56 AM
    Wednesday, January 13, 2016 8:55 AM
  • Thanks Kristin,

    It definitely is, thanks for the assistance

    Thursday, January 14, 2016 7:32 AM
  • HI BobbyZA,

    Please try the following query.

    var StoreSOH = from row in dt.AsEnumerable()
    
                            let id = row.Field<int>("Site")
    
                            group row by id into grp
    
                            orderby grp.Key
    
                            select new
    
                            {
    
                                id = grp.Key,
    
                                Date = grp.Max(t => t.Field<DateTime>("Date")),
    
                                Sum = grp.Sum(r => r.Field<int>("SOH"))
    
                            };
    
     
    
               
    
     
    
                var StoreSOH2 = from d in StoreSOH
    
                                join line in dt.AsEnumerable() on d.id equals line.Field<int>("Site")
    
                                where line.Field<DateTime>("Date") == d.Date
    
                                select new
    
                                {
    
                                    Site = d.id,
    
                                    Date = d.Date,
    
                                    SOH = line.Field<int>("SOH")
    
                                };
    

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by BobbyZA Friday, January 15, 2016 11:52 AM
    Friday, January 15, 2016 5:53 AM
  • Hi Fred,

    Thank you for this, I actually worked it out.

    I also found a fantastic tool to test LINQ queries called LINQPad, there are two versions depending on the .Net version. It really helps in separating the LINQ query, testing it and the code then can simply be pasted directly into you solution. Its created by Joseph Albahari, Ben Albahari and O'Reilly Media Inc. and can be found here

    Friday, January 15, 2016 12:01 PM
  • Hi Fred,

    Thank you for this, I actually worked it out.

    I also found a fantastic tool to test LINQ queries called LINQPad, there are two versions depending on the .Net version. It really helps in separating the LINQ query, testing it and the code then can simply be pasted directly into you solution. Its created by Joseph Albahari, Ben Albahari and O'Reilly Media Inc. and can be found here


    So you actually did not need the sum of SOH for each Site for latest dates?


    Friday, January 15, 2016 12:20 PM
  • Hi Viorel,

    Yes I do, the above code does that? The only thing that is when I run the code in LINQPad, it works 100% correctly. But in Studio it does not group the sites for some reason. I am not sure what the differences are between LINQPAd and Studio. This clearly creates a huge result and I can't find a way of grouping the sub-query (StoreSOH2).

    I understand the logic of you code, but it hangs on the date comparison for some reason. I have done some checks on the dates and they seem fine (plus my previous code worked) so I am not sure why "Sum = ( from r in grp where r.Field<DateTime>( "Date" ).Date == grp.Max( z => z.Field<DateTime>( "Date" ).Date ) select r.Field<int>( "SOH" ) ).Sum()" hangs?

    • Edited by BobbyZA Monday, January 18, 2016 6:11 AM
    Sunday, January 17, 2016 5:17 PM