Hi,

This is an HQL question, not specifically a Hibernate technical question.

I have a process that uses two HQL queries:

1) Select all item IDs of a particular type:
select item.id from PItem as item where item.type = ?
2) Run the query and get a collection of IDs.
3) For each returned ID, count how many entries it has in the audit table:
select count(*) from PAudit as audit where audit.itemId = ?
4) Create a pretty table with the audit counts for each item of interest.

This works as intended.

I want to combine the HQL into a single query. I've tried the following:
select count(audit), item.id from PItem as item, PAudit as audit where
item.type = ? and audit.itemId = item.id
but it chokes on the count(audit) with an Oracle exception about single-group group functions. (I can provide more info if needed, but I think understanding my HQL problem will allow me to avoid the exception altogether).

Is there another way to do this that functions correctly?

Thanks for any help you can provide,
BU

Hibernate version: 2.1.2
Mapping documents: N/A
Code between sessionFactory.openSession() and session.close(): N/A
Full stack trace of any exception that occurs: N/A
Name and version of the database you are using: Oracle 9i
The generated SQL (show_sql=true): N/A
Debug level Hibernate log excerpt: N/A

Think about how you'd do it in SQL (group by) - you can do just the same in HQL

Thanks for the help -- I'd completely overlooked group by. For the interested, the working query ended up being:

select count(*), item.id from PItem as item, PAudit as audit where
item.type = ? and audit.itemId = item.id group by item.id

Regards,
BU