Search code examples
c#linqgroup-bylinq-to-entities

Apply Group by on latest group


Please consider this table:

Organization           State          Year          Month        Value
----------------------------------------------------------------------
   O1                   NY             2017          1            1
   01                   WA             2017          1            2
   01                   SA             2017          1            3
   O1                   NY             2017          2            4
   01                   WA             2017          2            5
   01                   SA             2017          2            6
   O2                   NY             2015          9            7
   02                   WA             2015          9            8
   02                   SA             2015          9            9
   O2                   NY             2016          1            10
   02                   WA             2016          1            11
   02                   SA             2016          1            12
   O3                   NY             2017          8            13
   03                   WA             2017          8            14
   03                   SA             2017          8            15

I want to create this result:

Organization           Year          Month        Sum
------------------------------------------------------
    01                 2017            2           15
    02                 2016            1           33
    03                 2017            8           42

I want to group on latest Year, Month and calculate sum. In above sample Organization 01 has data for 2 periods but I want to group on latest period.


UPDATE 1)

var query = from o in MyList
            group o by new {c.Organization, c.Year , c.Month} int grp
            select new 
            { 
                grp.Key.Organization, 
                grp.Key.Year, 
                grp.Key.Month, 
                grp.Sum() 
            };

Solution

  • Try below query:

    class Program
    {
        static void Main(string[] args)
        {           
    
            var results = (from o in MyList
                           group o by new { o.Organization } into g
                           select new
                           {
                               Org_Id = g.Key.Organization,
                               Year = g.Select(x => x.Year)
                                       .Max(),
                               Month = g.Where(x => x.Year == g.Select(y => y.Year).Max())
                                        .Select(z => z.Month)
                                        .Max(),
                               Sum = g.Where(x => x.Month == g.Where(y => y.Year == g.Select(z => z.Year).Max())
                                                              .Select(y => y.Month)
                                                              .Max())
                                      .Select(z => z.Value)
                                      .Sum()
                           }).ToList();
    
            results.ForEach(x => Console.WriteLine($"Org_Id: {x.Org_Id} \t Year: {x.Year} \t Month: {x.Month} \t Sum: {x.Sum}"));
    
            Console.ReadLine();
        }
    }
    

    What we done in query:

    1) Group by Organization

    2) Org_Id: as key of your group

    3) Year: select Max year from group.

    4) Month: select Max of month by selecting Max of year from group.

    5) Sum: sum of value by selecting Max of month of Max of year from group.

    Output:

    enter image description here

    Sql for above query:

    SELECT [t1].[Organization] AS [Org_Id], (
        SELECT MAX([t2].[Year])
        FROM [Org] AS [t2]
        WHERE (([t1].[Organization] IS NULL) AND ([t2].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t2].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t2].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t2].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t2].[Organization]))))
        ) AS [Year], (
        SELECT MAX([t3].[Month])
        FROM [Org] AS [t3]
        WHERE ([t3].[Year] = ((
            SELECT MAX([t4].[Year])
            FROM [Org] AS [t4]
            WHERE (([t1].[Organization] IS NULL) AND ([t4].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t4].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t4].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t4].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t4].[Organization]))))
            ))) AND ((([t1].[Organization] IS NULL) AND ([t3].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t3].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t3].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t3].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t3].[Organization])))))
        ) AS [Month], (
        SELECT SUM([t5].[Value])
        FROM [Org] AS [t5]
        WHERE ([t5].[Month] = ((
            SELECT MAX([t6].[Month])
            FROM [Org] AS [t6]
            WHERE ([t6].[Year] = ((
                SELECT MAX([t7].[Year])
                FROM [Org] AS [t7]
                WHERE (([t1].[Organization] IS NULL) AND ([t7].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t7].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t7].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t7].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t7].[Organization]))))
                ))) AND ((([t1].[Organization] IS NULL) AND ([t6].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t6].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t6].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t6].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t6].[Organization])))))
            ))) AND ((([t1].[Organization] IS NULL) AND ([t5].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t5].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t5].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t5].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t5].[Organization])))))
        ) AS [Sum]
    FROM (
        SELECT [t0].[Organization]
        FROM [Org] AS [t0]
        GROUP BY [t0].[Organization]
        ) AS [t1]
    

    Output:

    enter image description here