locked
Group By RRS feed

  • Question

  • I have the following table:

    T1
    --------------
    Gru
    Art
    Q
    [Other fields]

    How to write a query using LINQ on Entity Framework model above which performs the same following result thats t-sql:

    SELECT Gru,Art, SUM(Q) AS Q

    FROM T1

    GROUP BY Gru,Art

    HAVING SUM(Q) <> 0

    ORDER BY Gru,Art

    Friday, November 18, 2011 11:37 AM

Answers

  • Hi,

    For this, just divide row.Q with 100 and it will solve itself.

     .Select(row => new { row.Key.Gru, row.Key.Art, Q = row.Sum(row2 => row2.Q / 100) })
    
    
    Other than this, my original answer is correct for your question.


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    • Marked as answer by Antfor Tuesday, November 22, 2011 10:15 AM
    Tuesday, November 22, 2011 10:01 AM

All replies

  • Hi!

    This should give the same code:

     LINQ method:

    var result = context.T1.GroupBy(row => new { row.Gru, row.Art })
                           .Where(row => row.Sum(row2 => row2.Q) != 0)
                           .OrderBy(row => row.Key.Gru).ThenBy(row => row.Key.Art)
                           .Select(row => new { row.Key.Gru, row.Key.Art, Q = row.Sum(row2 => row2.Q) })
                           .ToList();
    
    

    LINQ Query

    var result = from row in context.T1
                    group row by new { row.Gru, row.Art } into grp
                    where grp.Sum(row => row.Q) != 0
                    orderby grp.Key.Gru, grp.Key.Art
                    select new { grp.Key.Gru, grp.Key.Art, Q = grp.Sum(row => row.Q) };


    Hope this helps!

     


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.

    • Edited by Rune Gulbrandsen Friday, November 18, 2011 12:25 PM added LINQ Query
    • Marked as answer by Antfor Monday, November 21, 2011 6:07 PM
    • Unmarked as answer by Antfor Tuesday, November 22, 2011 8:26 AM
    • Proposed as answer by Fly_fish Tuesday, November 22, 2011 9:09 AM
    Friday, November 18, 2011 12:19 PM
  • This is Ok:

    var result = from row in context.T1
                    group row by new { row.Gru, row.Art } into grp
                    where grp.Sum(row => row.Q) != 0
                    orderby grp.Key.Gru, grp.Key.Art
                    select new { grp.Key.Gru, grp.Key.Art, Q = grp.Sum(row => row.Q) };
    

    But when Q is a decimal result of (for example) 18.00, the value in Q is 1800
    Note that Q in T1 is decimal(18,2)

    ¿How can I convert?

    Thank you again

     

     

     

     

    Monday, November 21, 2011 6:52 PM
  • If Q is Decimal, there is no need to convert, sum should work ok: http://msdn.microsoft.com/en-us/library/bb298138.aspx

    Else you can refer Convert.ToDecimal method: http://msdn.microsoft.com/en-us/library/system.convert.todecimal(v=VS.100).aspx

    Q = grp.Sum(row => Convert.ToDecimal(row.Q))


    I am fish.
    Tuesday, November 22, 2011 9:08 AM
  • The result in Q is 1800 and must be 18.00. I mean that if I format Q.tostring("##.##") the result is 1800.00 instead of 18.00
    Tuesday, November 22, 2011 9:14 AM
  • Hi,

    For this, just divide row.Q with 100 and it will solve itself.

     .Select(row => new { row.Key.Gru, row.Key.Art, Q = row.Sum(row2 => row2.Q / 100) })
    
    
    Other than this, my original answer is correct for your question.


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    • Marked as answer by Antfor Tuesday, November 22, 2011 10:15 AM
    Tuesday, November 22, 2011 10:01 AM
  • That's what I did (/100) but I expected something more scientific
    Tuesday, November 22, 2011 10:15 AM
  • Sometimes the easiest is the best :)
    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    Tuesday, November 22, 2011 10:20 AM