HQL query, MS SQL 2000 and Group By

deejay · **Posted:** Thu Dec 01, 2005 5:10 am

Hi!

I have troubles with running simple query using nhibernate:

Code:

// Selects list of customers with their orders counts
select customer, count(orders) from Customer customer 
    join customer.OrderList orders
    group by customer

I get error, which is typical to MS SQL Server:

Code:

NHibernate.ADOException: Could not execute query ---> System.Data.SqlClient.SqlException: Column 'customer0_.type_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And so on, all Customer fields are listed in this error.

Do I have to write ALL fields in HQL Group By?
This does not make sense ... Will this behaviour be fixed in next release?
I am using nHibernate 1.0

KPixel · **Posted:** Thu Dec 01, 2005 9:55 am

The problem is not in NHibernate but in your HQL query;
It looks like you can simply remove "group by customer"...

Anyway, to build complex queries, you may use NHibernate Query Analyzer (by Ayende); it is a very good tools for this kind of problems.

deejay · **Posted:** Thu Dec 01, 2005 3:09 pm

Thanks, I'll take a look.
[Edit]Checked and it did not work, got the same error again:

Code:

Column 'customer0_.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'customer0_.registered' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It's caused by MS SQL Server I guess - it needs all fields to be grouped.
For example, this works:

SELECT cust.id, cust.name, count(orders) as ordercount
FROM customers cust LEFT JOIN orders ON orders.customer_id=cust.customer_id
GROUP BY cust.id, cust.name, count(orders)
ORDER BY cust.name

this does not

SELECT cust.*, count(orders) as ordercount
FROM customers cust LEFT JOIN orders ON orders.customer_id=cust.customer_id
GROUP BY cust.id
ORDER BY cust.name

(but same query works on MySQL ;))

So, is it bug or feature?

radu · **Joined:** Wed Jun 15, 2005 4:17 am **Posts:** 156

your query should look like:

Code:

select customer, count(elements(customer.Orders)) from Customer customer

please consult the Hibernate documentation.

cheers,
Radu

radu · **Joined:** Wed Jun 15, 2005 4:17 am **Posts:** 156

oops, I forgot the "group by" clause:

Code:

select customer, count(elements(customer.Orders)) from Customer customer group by customer

Radu