Hi
I am trying to get sum of a column (Amount) in a table (Ledger) grouping on a parent column (Customer). Ledger table has Customer table as parent / look up table.
So I did
select c, sum(l.Amount) from Customer c, Ledger l where l.Customer = c group by c
I am actually using Castle ActiveRecord and using 'Import' attribute and pupulating a custom non mapped class. I have code working in some other way (the hql is different)
The reason of my post os that the above hql led to something similar to following native SQL
select customer_id, customer_name, address, sum(amount) from customer, ledger where ledger.customer_id = customer.customer_id group by customer.customer_id
I have simplified the query to keep things short.
This leads to error in SQL server because it expects customer_name and address also to be part of group by clause.
My question is when I use group by c, why isn't every column from customer table used for grouping?
I am not expert in nhibernate so I might be missing a lot.
Regards
PK
|