Group By Query

[Francesco]

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

[Viktor Zhivkov]

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

[Francesco]

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?

[Viktor Zhivkov]

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