I'm unable to get this to work:

sales.by.region.mtd = \
select \
sls.location, \
max(sls.salesDate), \
sum(sls.sales), \
sum(sls.costOfGoodsSold), \
sum(sls.transactionCount), \
sum(sls.lastYearsSales), \
max(sls.budgetedSales), \
max(sls.salesDaysInMonthToDate) \
from com.fubar.DailyLocationSales sls \
where sls.salesDate between ? and ? \
and sls.location.region.id = ? \
group by sls.location

sls.location is defined in the mapping file for DailyLocationSales as
<many-to-one name="location"
class="com.fubar.Location"
column="location_id"
outer-join="true"
not-null="true"
/>
The complaint is
java.sql.SQLException: ERROR: Attribute location1_.location_id must be GROUPed or used in an aggregate function

In other words, the sql trying to access the LOCATION table's location_id
rather than use the SALES table's location_id.

I see examples in the manual of using aggregate functions, and I see examples of using joins, but I see no examples of where the element that
is being joined on is in the GROUP BY clause.

What, if anything, am I missing?

try: select location.id, .... group by location.id

I was hoping to avoid this. I can do it, but then I no longer have DailyLocationSales objects (which own a full up Location, not just a LocationId).

Tried this and "select location.id, .... group by location.id" also fails with the same error message.

Looks a bit weird, but is semantically correct and works.

Thanks, but
Still no joy:

sales.by.region.mtd = \
select \
loc, \
max(sls.salesDate), \
sum(sls.sales), \
sum(sls.costOfGoodsSold), \
sum(sls.transactionCount), \
sum(sls.lastYearsSales), \
max(sls.budgetedSales), \
max(sls.salesDaysInMonthToDate) \
from com.fubar.DailyLocationSales sls \
join sls.location loc \
where sls.salesDate between ? and ? \
and loc.region.id = ? \
group by loc.id, loc.locationCode, loc.city, \
loc.open, loc.usingCounterpoint, loc.saturdayWeightInBudget, \
loc.sundayWeightInBudget

and we still get

java.sql.SQLException: ERROR: Attribute location1_.location_id must be GROUPed or used in an aggregate function

For some reason by the time we get down to SQL we've forgotten that
loc.id and location.location_id are mapped. It thinks we haven't grouped by this field, but we have.

There are several other non-simple properties of location, but none are relevant here.

by the way, in case you're curious:
location.id is defined as
<id name="id" type="int" unsaved-value="null" >
<column name="location_id" sql-type="serial" not-null="true"/>
<generator class="native"/>
</id>

Postgres version 7.3.4
Hibernate version 2.1.2

select i, max(b.amount.value) from Bid b join b.item as i group by i.id, i.name, i.description

This works for me, it's a many-to-one between Bid and Item.

Certainly seems that it should work, and yet it doesn't in my case. Unless you can come up with a better idea, I am going to drop down to bare SQL and do it that way. Seems like a bug to me.

I'm pretty sure its not a bug, but you haven't shown the full mapping and code yet.

The location mapping:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<!--
Document : Location.hbm.xml
-->

<hibernate-mapping>

<class name="com.fubar.Location"
table="location">

<!-- A 32 hex character is our surrogate key. It's automatically
generated by Hibernate with the identity pattern. -->
<id name="id" type="int" unsaved-value="null" >
<column name="location_id" sql-type="serial" not-null="true"/>
<generator class="native"/>
</id>

<property name="locationCode">
<column
name="location_code"
sql-type="char(3)"
not-null="true"
unique="true"
/>
</property>

<!-- the rest of this file is yet to be written. Not needed
for phase 1.
-->
<property name="city">
<column name="city" sql-type="varchar(25)" not-null="true"/>
</property>

<many-to-one name="state"
class="com.fubar.State"
column="state_id"
cascade="none"
outer-join="true"
not-null="false"
/> <!-- a location may be a dummy, having no state -->

<many-to-one name="payrollGroup"
class="com.fubar.PayrollGroup"
column="payroll_frequency_id"
cascade="none"
outer-join="true"
not-null="false"
/>

<many-to-one name="group"
class="com.fubar.LocationGroup"
column="group_id"
outer-join="true"
not-null="false"
/>

<many-to-one name="region"
class="com.fubar.LocationRegion"
column="region_id"
outer-join="true"
not-null="false"
/>

<many-to-one name="district"
class="com.fubar.LocationDistrict"
column="district_id"
outer-join="true"
not-null="false"
/>

<property name="open">
<column name="open" sql-type="boolean" not-null="true"/>
</property>
<property name="usingCounterpoint">
<column name="cp_flag" sql-type="boolean" not-null="true"/>
</property>

<property name="saturdayWeightInBudget" column="weight_in_budget_sat" type="int"/>
<property name="sundayWeightInBudget" column="weight_in_budget_sun" type="int"/>

</class>

</hibernate-mapping>

The DailyLocationSales mapping:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<!--
Document : DailyLocationSales.hbm.xml
-->

<hibernate-mapping>

<class name="com.fubar.DailyLocationSales"
table="daily_sales_v">

<!-- A 32 hex character is our surrogate key. It's automatically
generated by Hibernate with the identity pattern. -->
<id name="id" type="integer" unsaved-value="null" >
<column name="sales_id" sql-type="serial" not-null="true"/>
<generator class="native"/>
</id>

<many-to-one name="location"
class="com.fubar.Location"
column="location_id"
outer-join="true"
not-null="true"
/>

<property name="salesDate" column="sales_date" type="date" not-null="true"/>
<property name="sales" type="java.math.BigDecimal"/>
<property name="costOfGoodsSold" column="cost" type="java.math.BigDecimal"/>
<property name="transactionCount" column="txact_count" type="integer"/>
<property name="lastYearsSales" column="last_years_sales" type="java.math.BigDecimal"/>
<property name="budgetedSales" column="budgeted_sales" type="java.math.BigDecimal"/>
<property name="salesDaysInMonthToDate" column="sales_days_month_to_date" type="java.math.BigDecimal"/>

</class>

</hibernate-mapping>


Perhaps the most interesting point to consider here is that daily_sales_v is actually a view, not a table. Could that be part of the problem?

I have the same mapping. Check the Hibernate SQL log, this kind of query works as I have shown.

Could you be a little more specific about what you mean when you say you have the same mapping? Are you referring just to the relationship between the two classes? Do you mean that your mapping also uses a view? Does your mapping also have several many-to-ones hanging off the "one" side of the main main many-to-one? Does your mapping have as part of its where clause parameters from both sides of the main many-to-one?

I'm trying to sort out what is significant from what isn't.

Now (after logging the SQL) we're getting somewhere.

The above HQL Query with the above mappings produced this SQL (formatted for human readability):

select
location1_.location_id as location1_,
location1_.location_code as location2_,
location1_.city as city,
location1_.state_id as state_id,
location1_.payroll_frequency_id as payroll_5_,
location1_.group_id as group_id,
location1_.region_id as region_id,
location1_.district_id as district8_,
location1_.open as open,
location1_.cp_flag as cp_flag,
location1_.weight_in_budget_sat as weight_11_,
location1_.weight_in_budget_sun as weight_12_,
location1_.location_id as x0_0_,
max(dailylocat0_.sales_date) as x1_0_,
sum(dailylocat0_.sales) as x2_0_,
sum(dailylocat0_.cost) as x3_0_,
sum(dailylocat0_.txact_count) as x4_0_,
sum(dailylocat0_.last_years_sales) as x5_0_,
max(dailylocat0_.budgeted_sales) as x6_0_,
max(dailylocat0_.sales_days_month_to_date) as x7_0_
from daily_sales_v dailylocat0_
inner join location location1_ on
dailylocat0_.location_id=location1_.location_id
where (dailylocat0_.sales_date between ? and ? )
and(location1_.region_id=? )

Two questions arise:
1) why the second instance of location1_location_id
2) where's the group by clause? That's why we're getting the error message. Something is confusing hibernate's SQL generator. Can someone tell me what?

Further info:
this is the HQL hibernate creates (from the log):

hql.QueryTranslator: HQL: select loc, max(sls.salesDate), sum(sls.sales), sum(sls.costOfGoodsSold), sum(sls.transactionCount), sum(sls.lastYearsSales), max(sls.budgetedSales), max(sls.salesDaysInMonthToDate) from com.xpedx.xpediate.persist.DailyLocationSales sls join sls.location loc where sls.salesDate between ? and ? and loc.region.id = ?

Something has caused the original HQL query to truncate.

AHA! The query is read in from a properties file and the backslash at the end of the line ...and loc.region.id = ? \
had a space after it, truncating the query.

Fix that and it works, almost - but contrary to your earlier reply, Christian, I had to include every field of Location, not just the simple properties.

Thanks a lot for your help. I could never have found this on my own! However, I would like to request that the Hibernate team make it a little easier to create HQL for this situation. This one falls a little short in the ease of use area.

It's pretty close to SQL, and we discussed having a translation from 'GROUP BY location" to a SQL 'group by LOC_ID, LOC_NAME, LOC_FOO' and so on, but it is not easily doable in Hibernate2. We will get it for Hibernate3.