Group By Query

4 posts, 0 answers
  1. Francesco
    Francesco avatar
    2 posts
    Member since:
    Sep 2013

    Posted 18 Sep 2013 Link to this post

    Hello, I run the following query on the MySQL database

    Select PRACOD PRATTT,PRACCL,PRACLI PRAOPE,PRACAD,PRATIF,PRATOP,PRACES,PRACEE, ANADES,ANACIT FROM AVEPRA
    LEFT JOIN COIANA ON ANACOD = PRACCL
    WHERE AVEPRA = 1026
    GROUP BY PRACAD

    The entity AVEPRA contains fields PRACOD, PRATTT, PRACCL, PRACLI PRAOPE, PRACAD, PRATIF, PRATOP, PRACES, PRACEE, PRATLL, PRAUIL, PRANON, Pranom, PRADES

    The entity Coiana contains fields ANACOD, Anades, ANACIT, ANAVER, AnaCap, ANALOP, ANAMON, ANABOT, ANAUIT, ANARES

    BUT I CAN NOT USE GROUP BY CLAUSE
    How can I write in both C # and VB.Net
  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 19 Sep 2013 Link to this post

    Hello Francesco,

    I have tried to define a more understandable SQL query based on your one.
    Can you confirm that I got your intention right?
    select p.id, p.categoryId, p.product_name
    from product p
    left join category c on p.categoryId = c.id
    where p.product_name like 'c%'
    group by p.categoryId

    This query produces the following error message in SQL Management Studio indicating that you can select only columns added to the group by clause or aggregate functions over these columns:
    Msg 8120, Level 16, State 1, Line 1
    Column 'product.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    If you are referring to this exception then you can utilize OpenAccess to do the heavy lifting for you by issuing multiple queries that will load all the columns (properties) using a simple LINQ query like that:
    var query = from p in context.Products
             join c in context.Categories on p.CategoryID equals c.Id
             where p.ProductName.Contains("c")
             group p by p.CategoryID;
    or in VB.NET:
    Dim query = From p In context.Products
             Join c In context.Categories On p.CategoryID = c.Id
             Where p.ProductName.Contains("c")
             Group p By p.CategoryID

    Please note that this LINQ query will result in multiple SQL queries in order to load the data and work around the limitation in SQL.

    Regards,
    Viktor Zhivkov
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
  3. DevCraft banner
  4. Francesco
    Francesco avatar
    2 posts
    Member since:
    Sep 2013

    Posted 19 Sep 2013 Link to this post

    My company uses mysql that allows the execution of this query.
    How do I get the same result without specifying all the fields in the group by?
  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 24 Sep 2013 Link to this post

    Hi Francesco,

    While MySql allows you to run such query, I am not sure that the results are the ones that are expected.
    In that case MySql will return only the first row for each group ignoring any additional rows in the data.

    On the other hand adding every single column from the select clause to the group by will result in useless group clause that will return a group for each row in your table.

    If you want to get your data grouped by some criteria and you want to have the complete entities loaded then you should use the LINQ query that I have posted in my previous post.

    Regards,
    Viktor Zhivkov
    Telerik
    OpenAccess ORM Q3 2013 Beta is available for immediate download in your account. Get it now and play with the latest bits. See what's new >>
Back to Top